There are ways to work with multiple tables in the same query, we can use:

Sub-queries
Implicit Join
Join operators (INNER, OUTER…)

Joins

This section will cover methods to join or cbind (if you’re familiar with R) - What’s important to remember is that

UNION will rbind, in other words it’ll merge vertically, it’ll add rows that pass the criteria vertically, so we end up with the same width object but the length will vary.
JOIN will cbind. It will merge horizontally, so the column number will change (in most cases)

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

We’ll use this setup for examples:

dataset: SouthWind_database
left table: Orders
right table: Customers
related column: CustomerID

Orders Table

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

Customers Table

**Shippers Table**
CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

ShipperID	ShipperName	Phone
1	Speedy Express	(503) 555-9831
2	United Package	(503) 555-3199
3	Federal Shipping	(503) 555-9931

Inner Join

Syntax:

SELECT          column_name(s)
FROM            table1
INNER JOIN      table2
ON              table1.column_name = table2.column_name;

Example with Aliases

SELECT      B.Borrower_ID, B.Last_name, B.Country, L.Borr_ID, L.Loan_ID
FROM        BORROWER B
INNER JOIN  LOAN L
ON          B.Borrower_ID = L.Borr_ID

(INNER) JOIN & JOIN Both accomplish the same and return records that have matching values in both tables
It is very likely that some customers will have multiple orders in the Orders table
If you have multiple columns in each table that share the same name with columns in the other table(s) it’s best to include each Tables name in the command

We want ALL ORDERS from customers from Customers table that had an order in the Orders table, use CustomerID as the common column

SELECT          Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM            Orders
INNER JOIN      Customers
ON              Orders.CustomerID = Customers.CustomerID;

Output:

Number of Records: 196

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Berglunds snabbköp	8/12/1996
10280	Berglunds snabbköp	8/14/1996

multiple tables

What if we want to select all Orders with Customers and Shippers information?
It means we have to JOIN three tables
We already INNER JOIN two tables above now all we do is join that joint with another INNER JOIN command
Note: the first 4 lines are the same as the code chunk above EXCEPT the FROM is now a list, hence the ( )

SELECT          Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM           (( Orders
                INNER JOIN      Customers
                ON              Orders.CustomerID = Customers.CustomerID)
                
INNER JOIN      Shippers
ON              Orders.ShipperID = Shippers.ShipperID);

Output:

Number of Records: 196

OrderID	CustomerName	ShipperName
10290	Comércio Mineiro	Speedy Express
10284	Lehmanns Marktstand	Speedy Express
10388	Seven Seas Imports	Speedy Express
10390	Ernst Handel	Speedy Express

Left Join

Syntax:

SELECT          column_name(s)
FROM            table1
LEFT JOIN       table2
ON              table1.column_name = table2.column_name;

LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
Regardless of any matches with the right table, it will return at least ALL records from the left table

Example:

SELECT          Customers.CustomerName, Orders.OrderID
FROM            Customers
LEFT JOIN       Orders
ON              Customers.CustomerID=Orders.CustomerID
ORDER BY        (if any);

Right Join

Syntax

SELECT          column_name(s)
FROM            table1
RIGHT JOIN      table2
ON              table1.column_name = table2.column_name;

RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
Regardless of any matches with the left table, it will return at least ALL records from the right table

Full Join

Syntax:

SELECT          column_name(s)
FROM            table1
FULL OUTER JOIN table2
ON              table1.column_name = table2.column_name
WHERE           condition (if any);

FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
FULL OUTER JOIN & FULL JOIN are the same
Note: The FULL OUTER JOIN returns all matching records from both tables whether the other table matches or not. So, if there are rows in table1 that do not have matches in table2, or if there are rows in table2 that do not have matches in table1, those rows will be listed as well.
Be aware that this type of join might return a very large result

Example:

SELECT           Customers.CustomerName, Orders.OrderID
FROM             Customers
FULL OUTER JOIN  Orders
ON               Customers.CustomerID = Orders.CustomerID
ORDER BY         Customers.CustomerName;

Output:

CustomerName	OrderID
Null	10309
Null	10310
Alfreds Futterkiste	Null
Ana Trujillo Emparedados y helados	10308
Antonio Moreno Taquería	Null

Self Join

Syntax:

SELECT    column_name(s)
FROM     table1 T1, table1 T2
WHERE    condition;

Self Join is a regular join but the table is joined with itself
In the code above, T1 and T2 are aliases of the same table

Customers table:

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

Example:

Let’s extract all unique customers that are from the same city
So, using the same table we’d set a condition that CustomerID are not equal -> unique
We’d set the City to be equal -> we want all unique customers from they city being the same
To pull their names we SELECT Customer Name as well

SELECT          A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM            Customers A, Customers B
WHERE           A.CustomerID <> B.CustomerID
AND             A.City = B.City
ORDER BY        A.City;

Output:

CustomerName1	CustomerName2	City
FISSA Fabrica Inter. Salchichas S.A.	Bólido Comidas preparadas	Madrid
Bólido Comidas preparadas	FISSA Fabrica Inter. Salchichas S.A.	Madrid
Bólido Comidas preparadas	Romero y tomillo	Madrid
FISSA Fabrica Inter. Salchichas S.A.	Romero y tomillo	Madrid
Ana Trujillo Emparedados y helados	Antonio Moreno Taquería	México D.F.
Pericles Comidas clásicas	Centro comercial Moctezuma	México D.F.
Antonio Moreno Taquería	Centro comercial Moctezuma	México D.F.
Antonio Moreno Taquería	Tortuga Restaurante	México D.F.
Centro comercial Moctezuma	Tortuga Restaurante	México D.F.

Implicit Joins

This section crosses between subquery and joins, it is more a join than a subquery so I’ll include it here as well as in the subquery page.

2 Tables in From

Consider what would happen if we used 2 tables in the From statement
This will result in a FULL JOIN - Cartesian Join
You will end up with more rows than for each table individually

Every row from the first table is joined with every row from the second table, no common keys/columns are used, so you can imagine how big can become if we have two large tables

SELECT  *
FROM    Employees, Departments

Filter

Lets limit the results of the join using additional operands. We want the rows where dept_id matches dep_id. Note the use of table names to prefix with every column name

SELECT  *
FROM    Employees, Departments
WHERE   Employees.dep_id = Departments.dept_id;

Alias

Since table names can be long and our statements can become long and complicated, we can use aliases without the use of AS

SELECT  *
FROM    Employees E, Departments D
WHERE   E.dep_id = D.dept_id;

# __ To subset even further we can narrow the SELECT statement
SELECT  Emp_id, Dep_name
FROM    Employees E, Departments D
WHERE   E.dep_id = D.dept_id;

Summary

Implicit Inner Join SELECT column_name(s) FROM table1, table2 WHERE table1.column_name = table2.column_name; Implicit Inner Join combines two or more records but displays only matching values in both tables. Inner join applies only the specified columns. SELECT * FROM employees, jobs where employees.job_id = jobs.job_ident;

Implicit Cross Join SELECT column_name(s) FROM table1, table2; Implicit Cross Join is defined as a Cartesian product where the number of rows in the first table is multiplied by the number of rows in the second table. SELECT * FROM employees, jobs;

Topic	Syntax	Description	Example

Cross Join	`SELECT column_name(s) FROM table1 CROSS JOIN table2;`	The `CROSS JOIN` is used to generate a paired combination of each row of the first table with each row of the second table.	`SELECT DEPT_ID_DEP, LOCT_ID FROM DEPARTMENTS CROSS JOIN LOCATIONS;`
Inner Join	`SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; WHERE condition;`	You can use an `inner join` in a SELECT statement to retrieve only the rows that satisfy the join conditions on every specified table.	`select E.F_NAME,E.L_NAME, JH.START_DATE from EMPLOYEES as E INNER JOIN JOB_HISTORY as JH on E.EMP_ID=JH.EMPL_ID where E.DEP_ID ='5';`
Left Outer Join	`SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;`	The `LEFT OUTER JOIN` will return all records from the left side table and the matching records from the right table.	`select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME from EMPLOYEES AS E LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;`
Right Outer Join	`SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;`	The `RIGHT OUTER JOIN` returns all records from the right table, and the matching records from the left table.	`select E.EMP_ID,E.L_NAME,E.DEP_ID,D.DEP_NAME from EMPLOYEES AS E RIGHT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;`
Full Outer Join	`SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;`	The `FULL OUTER JOIN` clause results in the inclusion of rows from two tables. If a value is missing when rows are joined, that value is null in the result table.	`select E.F_NAME,E.L_NAME,D.DEP_NAME from EMPLOYEES AS E FULL OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP;`
Self Join	`SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;`	A `self join` is regular join but it can be used to joined with itself.	`SELECT B.* FROM EMPLOYEES A JOIN EMPLOYEES B ON A.MANAGER_ID = B.MANAGER_ID WHERE A.EMP_ID = 'E1001';`

Joins in MySQL using phpMyAdmin

Full Outer Join

SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition

UNION

SELECT column_name(s)FROM table1RIGHT OUTER JOIN table2ON table1.column_name = table2.column_nameWHERE condition

The UNION operator is used to combine the result-set of two or more SELECT statements.

select E.F_NAME,E.L_NAME,D.DEP_NAME from EMPLOYEES AS E LEFT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP

UNION

select E.F_NAME,E.L_NAME,D.DEP_NAMEfrom EMPLOYEES AS ERIGHT OUTER JOIN DEPARTMENTS AS D ON E.DEP_ID=D.DEPT_ID_DEP