JOIN in SQL
JOIN is an SQL operation that combines data from two or more tables based on relationships between columns. JOIN is the most powerful features of SQL to combine data from multiple tables. In this article, we will explore what SQL JOINs are, why they are useful and how to use them efficiently with detailed examples. After reading this article, you will be able to use JOINs easily.
Syntax
SELECT column_1, column_2, ............... FROM table_1 JOIN table_2 ON table_1.column_x = table2.column_y;
Types of SQL JOINs
There are many types of Joins in SQL. These different types of JOINs are designed to address various scenarios based on the relationship between the tables and the kind of result required. Let's take a closer look at the most commonly used JOINs and their purposes with syntax.
INNER JOIN
INNER JOIN is the default type of join that returns the intersection of two tables. It works by comparing each row from first table with each row from second table and only combines rows that meet the join condition. It is the most commonly used JOIN as it includes only the rows where a match exists in both tables.
Syntax
SELECT column_1, column_2, ...........
FROM table_1
INNER JOIN table_2
ON table_1.column_x = table2.column_y;
OR
SELECT column_1, column_2, ..........
FROM table_1
JOIN table_2
ON table_1.column_x = table2.column_y;LEFT JOIN (or LEFT OUTER JOIN)
LEFT JOIN returns all rows from left table (first table) and the matching rows from right table ( second table). If there is no match found in between left and right table based on defined join condition, the result will still include all rows from left table but the columns from the right table will contain NULL values. This type of join is useful when you want to retain all records from left table, even when there is no corresponding match in right table.
Syntax
SELECT column_1, column_2, ............... FROM table_1 LEFT JOIN table_2 ON table_1.column_x = table2.column_y;
RIGHT JOIN (or RIGHT OUTER JOIN)
RIGHT JOIN returns all rows from the right table (second table) and the matching rows from the left table (first table). If no match is found between the right and left tables based on the defined join condition, the result will still include all rows from the right table, with NULL values in the columns from the left table. This type of join is useful when you want to retain all records from the right table, even when there is no corresponding match in left table.
Syntax
SELECT column_1, column_2, ............... FROM table_1 RIGHT JOIN table_2 ON table_1.column_x = table2.column_y;
FULL JOIN (or FULL OUTER JOIN)
FULL JOIN returns all rows from both left and right tables, including matching rows and if there is no match, NULL values are returned for columns from the table with no match. This join combines the results of both LEFT JOIN and RIGHT JOIN, ensuring that all records from both tables are retained, even when there is no corresponding match in the other table. The result set will contain all the rows from both tables.
Syntax
SELECT column_1, column_2, ............... FROM table_1 FULL JOIN table_2 ON table_1.column_x = table2.column_y;
CROSS JOIN
CROSS JOIN returns the Cartesian product of the two tables. This means that each row from first table is combined with every row from second table. This can result in a very large result set. It's useful when you need every combination of rows from both tables which can be useful for generating combinations, testing or certain types of reports. Be careful when using CROSS JOIN as it can produce large and potentially unwanted result sets.
Syntax
SELECT column_1, column_2, ............... FROM table_1 CROSS JOIN table_2;
SELF JOIN
SELF JOIN is a type of join where a table is joined with itself. This can be useful when you need to compare rows within the same table or find relationships between data in the same table. In this case, the table is treated as two separate tables by temporarily renaming at least one instance of the table in SQL query.
Syntax
SELECT column_1, column_2, ............... FROM table_1 t1 JOIN table_2 t2 ON t1.column_x = t2.column_y;
Key Characteristics of SQL JOINs
- SQL JOINs allow you to retrieve and combine data from two or more tables based on a related column between them.
- You can join more than two tables in a single query.
- In OUTER JOINs (LEFT, RIGHT and FULL) rows that do not have a match will return NULL for the columns of the table that does not have a corresponding entry.
- Some JOINs like CROSS JOINs can generate large result sets and may affect performance. It’s important to use JOINs efficiently to avoid unnecessary data overload specially on production environment.
- You can join tables with the results of subqueries as per your requirement.
- When working with multiple tables. It's important to apply filters (using WHERE clauses) as early as possible to minimize the dataset size and enhance performance.
- You can use different comparison operators and conditions in the ON clause.
- When joining tables, SQL matches rows from each table based on a join condition (e.g. ON table_1.id = table_2.id). If the columns used in the join have indexes, the database can efficiently locate the matching rows, rather than scanning the entire table.
- Indexing can greatly speed up join operations by making row lookups faster, reducing the need for full table scans and optimizing query execution.
- It’s important to only create indexes on columns that are frequently used in join conditions, WHERE clauses or ORDER BY clauses. Over-indexing can reduce overall performance.