An SQL JOIN clause combines records from two or more tables in a database.



////////////////
Inner join
////////////////

Default join-type that returns rows when there is at least one match in both tables.

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_>

SELECT employee.first_name
FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
////////////////
Left join
////////////////

Returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_>
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

////////////////
Right join
////////////////

Return all rows from the right table, even if there are no matches in the left table

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_>
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

////////////////
Full join
////////////////

Returns rows when there is a match in one of the tables

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_>
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
FULL JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName

////////////////
Union
////////////////

Combines two or more SELECT statements.

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

We want to list all the different employees in Norway and USA.

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

////////////////
Union All
////////////////

We want to list all employees in Norway and USA.

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

//////////////////////
References
//////////////////////

Quick Reference:


Take Quiz: