How to Join Two Tables in SQL: A Beginner's Guide
SQL joins are very powerful tools to combine data in two or more tables based on relational databases. It is common that information is in separate tables if you deal with a large amount of information to keep the database structure organized and effective. However, you will often find that you need to draw out or analyze information from two or more tables. SQL joins are your answer here.
We will also discuss the basics of joining two tables in SQL in this blog, including types of joins with examples and when to use each type.
A SQL join is one that can allow you to couple up rows from two or more tables based on related column values. You then, using the joining feature of a table, develop one united dataset by retrieving the information from several data sources. For example, consider you have a table known as Customers and Orders table, so by joining up them you create a view as of what each customer ordered.
2. Types of SQL Joins
SQL has a number of joins that you can use to combine tables:
1. INNER JOIN – Returns only rows where there is a match in both tables.
2. LEFT JOIN (or LEFT OUTER JOIN) – Returns all rows from the left table and matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.
3. RIGHT JOIN (or RIGHT OUTER JOIN) – It returns all the rows of the right table and the matched rows of the left table. If there is no match, it returns NULL values for the columns in the left table.
4. FULL JOIN (or FULL OUTER JOIN)- Returns rows where at least one of the columns of either left or right tables matches. If the corresponding data is not found in this table, NULL is filled accordingly.
Employees Table EmployeeID EmployeeName DepartmentID |------------|--------------|--------------| 1 | Alice | 101 | 2 | Bob | 102 | 3 | Charlie | NULL
Departments Table | DepartmentID | DepartmentName | |--------------|----------------| | 101 | HR | | 102 | Sales | | 103 | Marketing |
Example 1: INNER JOIN
An INNER JOIN returns all rows where at least there exists match in both of the given tables.
Employees.EmployeeName, Departments.DepartmentName FROM Employees INNER JOIN Departments
ON Employees.DepartmentID=Departments.DepartmentID ;
Result: | EmployeeName | DepartmentName | |--------------|----------------| | Alice | HR | | Bob | Sales |
Only Alice and Bob are returned as output because they have only one common DepartmentID in Departments.
Example 2: LEFT JOIN
A LEFT JOIN returns all the rows of the left table, Employees, and the corresponding rows of the right table, Departments. If there are no rows in which the columns match, then the columns of the right table are filled with NULL.
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Result: | EmployeeName | DepartmentName | |--------------|----------------| | Alice | HR | | Bob | Sales | | Charlie | NULL |
Charlie is included, although he doesn't have a matching DepartmentID because NULL replaces DepartmentName.
Example 3: RIGHT JOIN
A RIGHT JOIN returns all rows of the right table, i.e., Departments and all rows in the left table, i.e., Employees that have a matching DepartmentID in the right table.
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Result: | EmployeeName | DepartmentName | |--------------|----------------| | Alice | HR | | Bob | Sales | | NULL | Marketing |
Even if no employee exists, the department "Marketing" is visible.
Example 4: FULL JOIN
A FULL JOIN returns all rows from both the tables, if there is any match between them. They fill the NULLs wherever no match exists.
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
Result: | EmployeeName | DepartmentName | |--------------|----------------| | Alice | HR | | Bob | Sales | | Charlie | NULL | | NULL | Marketing |
This result combines the LEFT JOIN and RIGHT JOIN results, showing all employees and all departments.
4. Choosing the Right Join for Your Query
Understanding when to use each join type depends on the question you’re trying to answer:
Use INNER JOIN when you only need rows with data present in both tables.
Use LEFT JOIN when you want all rows from the left table and matched rows from the right.
Use RIGHT JOIN if you want all rows of the right table and their corresponding rows from the left table.
Use FULL JOIN if you want all data from both tables regardless of a match being there.
Conclusion
Joining tables in SQL is a bit complex, but you soon begin to see how powerful it really is. Knowing what type of join to use gives you some flexibility in getting data from multiple tables, so you can really pick apart your database with flexible analysis and access methods for deeper insights into the information.
With that, you have the basics now and are all set to go at joins in your SQL queries. Take these practices seriously on live datasets so you really get to nail down those ideas and be more confident in using them.
