MySQL – Page 3 – Alejandro G. Carlstein Ramos Mejia Blog

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

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

SQL Join Types: Left (Outer) Join with Exclusion

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
  LEFT JOIN Orders
    ON Customers.CustomerID=Orders.CustomerID
WHERE Orders.CustomerID IS NULL
  AND 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	OrderID
NULL	Alfreds Futterkiste	NULL	NULL

Leave a comment

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

SQL Join Types: Left (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	11/27/1996	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
  LEFT 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
NULL	Alfreds Futterkiste	NULL
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

Alejandro G. Carlstein Ramos Mejia February 6, 2012 February 12, 2012 About Programming / MySQL

Category: MySQL

SQL Join Types: Right (Outer) Join with Exclusion

Tables

Table 1: Employees

Table 2: Orders

Query

Result

SQL Join Types: Right (Outer) Join

Tables

Table 1: Customers

Table 2: Orders

Query

Result

SQL Join Types: Left (Outer) Join with Exclusion

Tables

Table 1: Customers

Table 2: Orders

Query

Result

SQL Join Types: Left (Outer) Join

Tables

Table 1: Customers

Table 2: Orders

Query:

Result:

Protected: MySQL Tutorial – Part 4