SQL Joins | Explained With Examples

SQL Joins are essential for combining data from multiple tables based on a related column. They help retrieve meaningful information by linking tables efficiently.

What is an SQL Join?

An SQL Join is a method used to combine rows from two or more tables based on a related column. By using joins, you can extract useful insights from relational databases where data is distributed across different tables. The output is a result-set that contains data from multiple tables that share a common relationship.

Types of SQL Joins

SQL supports several types of joins, each serving a specific purpose and with its own SQL command.

1. INNER JOIN

Returns only the matching records between both tables, excluding non-matching rows. This is the most commonly used join in SQL.

Syntax:

SELECT table1.column, table2.column
FROM table1
INNER JOIN table2 ON table1.common_column = table2.common_column;

Example: Suppose you have employees and departments tables.

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

This query retrieves employees along with their department names.

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all records from the left table and only matching records from the right table. Non-matching rows in the right table return NULL values.

Syntax:

SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;

Example: Filtering with WHERE Clause

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NOT NULL;

This query retrieves only customers who have placed orders by filtering out NULL values.

3. RIGHT JOIN (RIGHT OUTER JOIN)

Returns all records from the right table and only matching records from the left table. Non-matching rows in the left table return NULL values.

Syntax:

SELECT table1.column, table2.column
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

Example:

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

This retrieves all departments, even those with no employees.

4. FULL JOIN (FULL OUTER JOIN)

Returns all records when there is a match in either left or right table. Non-matching rows in either table return NULL values.

Syntax:

SELECT table1.column, table2.column
FROM table1
FULL JOIN table2 ON table1.common_column = table2.common_column;

Example:

SELECT customers.customer_name, orders.order_id
FROM customers
FULL JOIN orders ON customers.customer_id = orders.customer_id;

This retrieves all customers and all orders, even if there are no matches.

5. CROSS JOIN

Returns the Cartesian product of both tables, meaning every row from the first table is combined with every row from the second table.

Syntax:

SELECT table1.column, table2.column
FROM table1
CROSS JOIN table2;

Example:

SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

This creates all possible combinations of employees and departments.

Common Questions About SQL Joins

What are the four types of joins in SQL?

The four primary types of joins in SQL are:

• INNER JOIN – Returns only the matching rows from both tables.
• LEFT JOIN – Returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN – Returns all rows from the right table and matching rows from the left table.
• FULL JOIN – Returns all rows from both tables, filling unmatched values with NULL.

What is a JOIN in SQL?

A JOIN in SQL is used to combine data from two or more tables based on a related column. Joins help in retrieving meaningful data from multiple tables by linking them based on common values. The foreign key relationship between tables helps establish connections for meaningful joins.

Which JOIN is mostly used in SQL?

The INNER JOIN is the most commonly used join in SQL. It retrieves only the rows that have matching values in both tables, making it efficient for filtering relevant data.

Can we join 3 tables at a time in SQL?

Yes, you can join three or more tables in SQL by using multiple JOIN conditions.

Example:

SELECT employees.name, departments.department_name, locations.city
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
INNER JOIN locations ON departments.location_id = locations.location_id;

This query retrieves employee names along with their department names and location cities.

Choosing the Right Join

If you're unsure which joins to use, this is a simple SQL cheat sheet to refer back to:

• Use INNER JOIN when you need only matching rows.
• Use LEFT JOIN when you need all records from the left table.
• Use RIGHT JOIN when you need all records from the right table.
• Use FULL JOIN when you need all records from both tables.
• Use CROSS JOIN when you need all possible combinations of rows.

Using Aliases in Joins

Aliases can be used to shorten table names and improve query readability.

Example:

SELECT e.name, d.department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.department_id;

This query achieves the same result as previous examples but makes the query more readable.

Key Takeaways

• Joins allow combining data from multiple tables based on related columns.
• INNER JOIN returns only matching records, while OUTER JOINS include non-matching records with NULL values.
• FULL JOIN retrieves all records from both tables, while CROSS JOIN forms all possible row combinations.
• Choosing the right join depends on the relationship between your tables and the data you need to retrieve.
• Using aliases in select statements can make complex queries more readable.
• Joins work effectively with foreign keys, ensuring referential integrity.

Wrapping Up

Understanding SQL joins is crucial for working with relational databases. By mastering INNER, LEFT, RIGHT, FULL, and CROSS joins, you can efficiently query data and extract valuable insights. Knowing join syntax, filtering conditions using the WHERE clause, and using aliases can make complex queries more efficient and readable. Happy querying!