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:

  HtmlCopy code
  

• Departments table:

  HtmlCopy code
  

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

SqlCopy code

This query will return:

HtmlCopy code

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:

SqlCopy code

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

HtmlCopy code

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:

SqlCopy code

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:

SqlCopy code

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.