What is an SQL Join?
The SQL Join clause is used to combine rows from two or more related tables into a single result set based on a common column or columns, which is typically established through foreign key constraints.
The āonā keyword is used to specify the join condition, which specifies the relationship between the tables being joined.
There are 4 different types of SQL joins:
Note: A table can also join to itself, which is known as a Self Join.
As long as the condition is met, the INNER JOIN keyword selects all rows from both tables. This keyword will generate the result set by combining all rows from both tables that satisfy the condition, i.e. the value of the common field is the same.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Example:
Consider a product table,
ID | ITEM |
---|---|
1 | Ball |
2 | Chair |
3 | Board |
4 | Desk |
and the product_info table,
ID | QUANTITY |
---|---|
1 | 23 |
2 | 90 |
3 | 05 |
8 | 12 |
Query for Inner Join:
SELECT * from product INNER JOIN product_info where product.ID = product_info.ID;
Output:
ID | ITEM | ID | QUANTITY |
---|---|---|---|
1 | Ball | 1 | 23 |
2 | Chair | 2 | 90 |
3 | Board | 3 | 05 |
This join returns all rows from the table on the left side of the join and matches rows from the table on the right side of the join. The result set will contain null for the rows for which there is no matching row on the right side. LEFT JOIN is also known as LEFT OUTER JOIN.
Syntax:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;
In some databases, the OUTER keyword is omitted and written simply as LEFT JOIN.
Example:
Considering the product table
Query for Left Outer Join:
SELECT * FROM class LEFT OUTER JOIN product_info ON (product.ID = product_info.ID);
Output:
ID | ITEM | ID | QUANTITY |
---|---|---|---|
1 | Ball | 1 | 23 |
2 | Chair | 2 | 90 |
3 | Board | 3 | 05 |
4 | Desk | null | null |
RIGHT JOIN is similar to LEFT JOIN. This join returns all rows from the table on the right side of the join as well as matching rows from the table on the left side of the join. The result set will contain null for the rows for which there is no matching row on the left side. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;
In some databases, the OUTER keyword is omitted and written simply as LEFT JOIN.
Example:
Considering the product table
Query for Right Outer Join:
SELECT * FROM class RIGHT OUTER JOIN product_info ON (product.ID = product_info.ID);
Output:
ID | ITEM | ID | QUANTITY |
---|---|---|---|
1 | Ball | 1 | 23 |
2 | Chair | 2 | 90 |
3 | Board | 3 | 05 |
null | null | 8 | 12 |
FULL JOIN generates the result set by combining the results of both LEFT JOIN and RIGHT JOIN. The result set will include all the rows from both tables. The result set will contain NULL values for rows without a match.
Syntax:
SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;
In some databases, the OUTER keyword is omitted and written simply as LEFT JOIN.
Example:
Considering the product table
Query for Right Outer Join:
SELECT * FROM class FULL OUTER JOIN product_info ON (product.ID = product_info.ID);
Output:
ID | ITEM | ID | QUANTITY |
---|---|---|---|
1 | Ball | 1 | 23 |
2 | Chair | 2 | 90 |
3 | Board | 3 | 05 |
4 | Desk | null | null |
null | null | 8 | 12 |
Note: also read about SQL Constraints
Please follow me to read my latest post on programming and technology if you like my post.
https://www.instagram.com/coderz.py/
https://www.facebook.com/coderz.py
Staying up to the mark is what defines me. Hi all! Iām Rabecca Fatima a keen learner, great enthusiast, ready to take new challenges as stepping stones towards flying colors.
Given an integer A. Compute and return the square root of A. If A is…
Given a zero-based permutation nums (0-indexed), build an array ans of the same length where…
A heap is a specialized tree-based data structure that satisfies the heap property. It is…
What is the Lowest Common Ancestor? In a tree, the lowest common ancestor (LCA) of…
A Binary Search Tree (BST) is a type of binary tree that satisfies the following…