For an instructor lead, in-depth look at learning SQL click below.
Mastering SQL joins is a pivotal step for anyone seeking to elevate their SQL skills from beginner to intermediate. SQL joins allow you to retrieve data from two or more tables based on a related column between them. In this guide, we will dive into the different types of SQL joins, when to use them, and examples of how they work.
Understanding SQL Joins
In the SQL world, a JOIN clause is used to combine rows from two or more tables, based on a related column between them. There are four types of joins in SQL: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
|
1 2 3 4 5 6 7 |
-- SQL Join Syntax SELECT column1, column2... FROM table1 JOIN table2 ON table1.column = table2.column; |
INNER JOIN
The INNER JOIN selects records that have matching values in both tables. In other words, it gets the intersection of two tables.
Example:
|
1 2 3 4 5 6 |
SELECT Employees.LastName, Employees.FirstName, Orders.OrderID FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID; |
This SQL statement would return the last name, first name and order ID for all employees that have one or more orders.
LEFT JOIN (or LEFT OUTER JOIN)
The LEFT JOIN selects records from the first (left-most) table and any matching records from the second (right-most) table.
Example:
|
1 2 3 4 5 6 |
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders LEFT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID; |
This SQL statement would return all order IDs and the first name and last name of any associated employees. If an order has no associated employee, the employee details will show as NULL.
RIGHT JOIN (or RIGHT OUTER JOIN)
The RIGHT JOIN selects records from the second (right-most) table and any matching records from the first (left-most) table.
Example:
|
1 2 3 4 5 6 |
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID; |
This SQL statement would still return all order IDs, and the first name and last name of any associated employees. But this time, if an employee has no associated orders, the order details will show up as NULL.
FULL JOIN (or FULL OUTER JOIN)
The FULL JOIN returns records when there is a match in either the first (left-most) table or the second (right-most) table.
Example:
|
1 2 3 4 5 6 |
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders FULL JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID; |
This SQL statement would return all order IDs, the first name and last name of any associated employees. If there are any orders or employees without a match, the details will show up as NULL.
Conclusion
Mastering SQL joins can make your life easier when dealing with complex database structures and can vastly improve your ability to deliver powerful and efficient SQL queries. As with any skill, the key to mastering SQL joins is practice, practice, and more practice.