coderz.py

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.

Types Of Joins:

There are 4 different types of SQL joins:

• SQL INNER JOIN (sometimes called a simple join)
• SQL LEFT OUTER JOIN (sometimes called LEFT JOIN)
• SQL RIGHT OUTER JOIN (sometimes called RIGHT JOIN)
• SQL FULL OUTER JOIN (sometimes called FULL JOIN)

Note: A table can also join to itself, which is known as a Self Join.

INNER JOIN (simple 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

LEFT JOIN(left outer join):

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(right outer join):

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(full outer join):

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

Follow Me

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