SQL Join Types: Two Inner Joins

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 

Table 3: Shippers

ShipperID

ShipperName

Phone

Speedy Express 

(503) 555-9831 

United Package 

(503) 555-3199 

Federal Shipping 

(503) 555-9931 

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, Shippers.ShipperName
FROM ((Orders
	INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
	INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID)
WHERE Orders.OrderId IN (10308, 10309, 10310, 10365);

Result

OrderID

CustomerName

ShipperName

10309 

Hungry Owl All-Night Grocers 

Speedy Express 

10365 

Antonio Moreno Taquería 

United Package 

10310 

The Big Cheese 

United Package 

10308 

Ana Trujillo Emparedados y helados 

Federal Shipping 

Share
Leave a comment

SQL Join Types: Full (Outer) Join with Exclusion

Tables

Table 1: Customers

CustomerId

FirstName

LastName

BookId

1

Joe

Blow

1

2

Jane

Doe

2

3

Harry

Crow

2

4

Jeffrey

Snow

0

Table 2: Books

BookId

OrderTitle

1

Star Wars

2

Jurassic Park

3

Little Women

4

Tom Sawyer

Query

SELECT *
FROM Customers
  FULL OUTER JOIN Books
    ON Customers.BookId = Books.BookId
WHERE Customers.CustomerId IS NULL
  OR Books.BookId IS NULL

Result

id

firstname

lastname

book_id

id1

title

1

Jeffrey

Snow

0

NULL

NULL

NULL

NULL

NULL

NULL

3

Little Women

NULL

NULL

NULL

NULL

4

Tom Sawyer

Share
Leave a comment

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
Leave a comment

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
Leave a comment

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
Leave a comment