SQL Join Types: Cross Join

Tables

Table 1: Foods

ItemId

ItemName

ItemUnit

CompanyId

1

Mixing

Pcs

16

2

Bananas

Pcs

15

3

Munching

Pcs

17

Basmati Rice 

Pcs

15

Cheese Cake

Pcs

18

6

Brie Cheese

Pcs

NULL

7

Salt & Pepper Chips

Pcs

15

Table 2: Orders

CompanyId

CompanyName

CompanyCity

18

Kellog

Florida

15

Nestle

Paris

16

Trader Joe

Denver

17

Wegmans

New York

19

Bits and Bytes

Buenos Aires

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

Query

SELECT Foods.ItemName, Foods.ItemUnit, Company.CompanyName, Company.CompanyCity 
FROM Foods 
CROSS JOIN Company; 

Result

ItemName

ItemUnit

CompanyName

CompanyCity

Mixing

Pcs

Mixing

Florida

Bananas

Pcs

Mixing

Florida

Munching

Pcs

Mixing

Florida

Mixing

Pcs

Trader Joe

Denver

Bananas

Pcs

Trader Joe

Denver

Munching

Pcs

Trader Joe

Denver

Share

SQL Join Types: Full (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

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 Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders 
  ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;

Result

CustomerName

OrderID

Alfreds Futterkiste

 NULL

Ana Trujillo Emparedados y helados

10308

Antonio Moreno Taquería

10365

 NULL

10382

 NULL

10351

Share

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

Share

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

Share

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 

1996-11-27

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 

Share