For an instructor lead, in-depth look at learning SQL click below.
SQL is a notable language used in programming, data manipulation, and for managing large amounts of data stored in relational database management systems. One of its most powerful features is the ability to join data from multiple tables into one comprehensive set. The aim of this article is to demystify the different kinds of joins offered in SQL and to provide a comprehensive guide for any SQL enthusiast or novice programmer.
What is a JOIN?
JOIN is a keyword in SQL used to combine rows from two or more tables. It is mainly based on a related column between them. This ability to combine tables provides developers the flexibility to retrieve data from different parts of the database in a single query.
Types of joins:
There are mainly four types of join in SQL:
1. INNER JOIN
2. LEFT JOIN / LEFT OUTER JOIN
3. RIGHT JOIN / RIGHT OUTER JOIN
4. FULL JOIN / FULL OUTER JOIN
INNER JOIN:
|
1 2 3 4 5 6 |
/* Selects records that have matching values in both tables */ SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; |
An INNER JOIN combines rows from two tables whenever there are matching values in a common field.
LEFT (OUTER) JOIN:
|
1 2 3 4 5 6 |
/* Select all records from the left table, and the matched records from the right table */ SELECT Orders.OrderID, Customers.CustomerName FROM Orders LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID; |
A LEFT JOIN allows you to obtain all the rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the right side of the result table.
RIGHT (OUTER) JOIN:
|
1 2 3 4 5 6 |
/* Select all records from the right table, and the matched records from the left table */ SELECT Orders.OrderID, Customers.CustomerName FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID; |
Similarly, a RIGHT JOIN provides all the rows from the right table, and the matched rows from the left table.
FULL (OUTER) JOIN:
|
1 2 3 4 5 6 |
/* Selects all records when there is a match in either left or right table */ SELECT Orders.OrderID, Customers.CustomerName FROM Orders FULL OUTER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; |
A FULL JOIN, also known as FULL OUTER JOIN, produces the set of all records in Table A and Table B, with matching records from both sides. If there is no match, the missing side will contain NULL.
Conclusion
This guide only scratches the surface of SQL JOINs. As you delve deeper into SQL, you’ll encounter more complex variations. Practice regularly and try out different queries. Happy joining!