How Do You Combine Two Tables in SQL?

Combining two tables in SQL is a fundamental skill for anyone working with relational databases. This process, often referred to as "joining" tables, allows you to see related data across different sources within your database. There are several types of joins available in SQL, each serving different purposes. Here, we'll explore the most common methods: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN, along with examples to illustrate their usage.

INNER JOIN

An INNER JOIN returns only the rows that have matching values in both tables. It's the most common type of join. Here's a basic example:

Imagine you have two tables: Employees and Departments.

• Employees table:

  textCopy

  1EmployeeID | Name       | DepartmentID
  2------------|------------|--------------
  31           | Alice      | 1
  42           | Bob        | 2
  53           | Charlie    | 1
  64           | David      | 3

• Departments table:

  textCopy

  1DepartmentID | DepartmentName
  2---------------|----------------
  31              | HR
  42              | Engineering
  53              | Sales

To list employees with their department names, you can use:

1SELECT Employees.Name, Departments.DepartmentName
2FROM Employees
3INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query will return:

1Name     | DepartmentName
2---------|----------------
3Alice    | HR
4Bob      | Engineering
5Charlie  | HR
6David    | Sales

LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for the right table's columns. Using the same tables, a LEFT JOIN would look like this:

1SELECT Employees.Name, Departments.DepartmentName
2FROM Employees
3LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

The result will include all employees, even those with no department:

1Name     | DepartmentName
2---------|----------------
3Alice    | HR
4Bob      | Engineering
5Charlie  | HR
6David    | Sales

If there were an employee without a department, NULL would appear in the DepartmentName column for that employee.

RIGHT JOIN

A RIGHT JOIN is similar to LEFT JOIN but returns all rows from the right table and the matched rows from the left table. If there’s no match, NULL values appear for the left table's columns. Here's how you would write it:

1SELECT Employees.Name, Departments.DepartmentName
2FROM Employees
3RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This query ensures you get all departments listed, including those without any employees.

FULL OUTER JOIN

A FULL OUTER JOIN returns all records when there is a match in either left or right table records. If there's no match, NULL values are returned for the table without a match. The syntax looks like this:

1SELECT Employees.Name, Departments.DepartmentName
2FROM Employees
3FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

This join will include all employees and all departments, showing NULLs where there are no matches on either side.

Joining tables is integral to querying relational databases effectively. Understanding the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN is critical for data retrieval based on relationships. Use each join type depending on the specific needs of your data analysis or application development.