Alejandro G. Carlstein Ramos Mejia Blog – Page 3 – 'Be the change you want to see in the world' by Mohandas Karamchand Gandhi

Alejandro G. Carlstein Ramos Mejia April 2, 2019 April 3, 2019 Database / MSSQL / MySQL / SQL

SQL Join Types: Anti-Semi Join

Tables

Table 1: Suppliers

SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country	Phone
10	Refrescos Americanas LTDA	Carlos Diaz	Av. das Americanas 12.890	São Paulo	5442	Brazil	(11) 555 4640
21	Lyngbysild	Niels Petersen	Lyngbysild Fiskebakken 10	Lyngby	2800	Denmark	43844108
22	Zaanse Snoepfabriek	Dirk Luchte	Verkoop Rijnweg 22	Zaandam	9999 ZZ	Netherlands	(12345) 1212

Table 2: Products

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
24	Guaraná Fantástica	10	1	12 – 355 ml cans	4.5
45	Røgede sild	21	8	1k pkg.	9.5
46	Spegesild	21	8	4 – 450 g glasses	12
47	Zaanse koeken	22	3	10 – 4 oz boxes	9.5
48	Chocolade	22	3	10 pkgs.	12.75

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT SupplierName
FROM Suppliers
WHERE NOT EXISTS (
  SELECT ProductName 
  FROM Products 
  WHERE SupplierId = Suppliers.supplierId 
    AND Price > 13
);

Result

SupplierName

Refrescos Americanas LTDA

Lyngbysild

Zaanse Snoepfabriek

Leave a comment

Alejandro G. Carlstein Ramos Mejia April 2, 2019 April 3, 2019 Database / MSSQL / MySQL / SQL

SQL Join Types: Semi Join

Tables

Table 1: Suppliers

SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country	Phone
6	Mayumi’s	Mayumi Ohno	92 Setsuko Chuo-ku	Osaka	545	Japan	(06) 431-7877
10	Refrescos Americanas LTDA	Carlos Diaz	Av. das Americanas 12.890	São Paulo	5442	Brazil	(11) 555 4640
15	Norske Meierier	Beate Vileid	Hatlevegen 5	Sandvika	1320	Norway	(0)2-953010

Table 2: Products

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
13	Konbu	6	8	2 kg box	6
14	Tofu	6	7	40 – 100 g pkgs.	23.25
24	Guaraná Fantástica	10	1	12 – 355 ml cans	4.5
33	Geitost	15	4	500 g	2.5

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT SupplierName
FROM Suppliers
WHERE EXISTS (
  SELECT ProductName 
  FROM Products 
  WHERE SupplierId = Suppliers.supplierId 
    AND Price < 7
);

Result

SupplierName

Mayumi’s

Refrescos Americanas LTDA

Norske Meierier

Leave a comment

Alejandro G. Carlstein Ramos Mejia April 2, 2019 April 3, 2019 Database / MSSQL / MySQL / SQL

SQL Join Types: Inner Join

Tables

Table 1: Orders

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2
10365	3	3	1996-11-27	2

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste).

Table 2: Customers

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
37	Hungry Owl All-Night Grocers	Patricia McKenna	8 Johnstown Road	Cork		Ireland
77	The Big Cheese	Liz Nixon	89 Jefferson Way Suite 2	Portland	97201	USA

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Customers.ContactName, Orders.OrderDate
FROM Orders
  INNER JOIN Customers 
    ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.OrderId IN (10308, 10309, 10310, 10365)
ORDER BY Orders.OrderId

Result

OrderID	CustomerName	ContactName	OrderDate
10308	Ana Trujillo Emparedados y helados	Ana Trujillo	1996-09-18
10309	Hungry Owl All-Night Grocers	Patricia McKenna	1996-09-19
10310	The Big Cheese	Liz Nixon	1996-09-20
10365	Antonio Moreno Taquería	Antonio Moreno	1996-11-27

Leave a comment

Alejandro G. Carlstein Ramos Mejia April 2, 2019 April 3, 2019 Database / MSSQL / MySQL / SQL

SQL Join Types: Right (Outer) Join with Exclusion

Tables

Table 1: Employees

EmployeeID	LastName	FirstName	BirthDate	Photo
8	Callahan	Laura	1/9/1958	EmpID8.pic
9	Dodsworth	Anne	7/2/1969	EmpID9.pic
10	West	Adam	9/19/1928	EmpID10.pic

Table 2: Orders

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10255	68	9	7/12/1996	3
10262	65	8	7/22/1996	3
10263	20	9	7/23/1996	3
10268	33	8	7/30/1996	3

Note: There is no record with EmployeeID = 10 (West Adam).

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
  RIGHT JOIN Employees
    ON Orders.EmployeeID = Employees.EmployeeID
WHERE Employees.EmployeeID IS NULL
ORDER BY Orders.OrderID;

Result

OrderID	LastName	FirstName
NULL	West	Adam

Leave a comment

Alejandro G. Carlstein Ramos Mejia April 2, 2019 April 3, 2019 Database / MSSQL / MySQL / SQL

SQL Join Types: Right (Outer) Join

Tables

Table 1: Customers

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
37	Hungry Owl All-Night Grocers	Patricia McKenna	8 Johnstown Road	Cork		Ireland
77	The Big Cheese	Liz Nixon	89 Jefferson Way Suite 2	Portland	97201	USA

Table 2: Orders

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2
10365	3	3	1996-11-27	2

Note: There is no order in which the CustomerID = 1 (Alfreds Futterkiste)

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Customers
  RIGHT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Customers.CustomerName 
  IN ('Alfreds Futterkiste', 'Ana Trujillo Emparedados y helados'
       , 'Antonio Moreno Taquería', 'The Big Cheese')
ORDER BY Customers.CustomerName;

Result

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10310	The Big Cheese	9/20/1996

Leave a comment