Visual Explanation of PL/SQL Joins

Tables may be joined in several ways. The most common technique is called an equijoin. A row is associated with one or more rows in another table based on the equality of column values or expressions. Tables may also be joined using a nonequijoin. In this case, a row is associated with one or more rows in another table if its column values fall into a range determined by inequality operators.

We'll take 2 tables, Table A and Table B

Table A			Table B
ID	Name		ID	Name
1	pardeep		1	oracle
2	kumar		2	pardeep
3	java		3	linkedin
4	latte		4	latte

INNER JOIN
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.
SQL INNER JOIN Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

or:

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

Example:
SELECT * FROM TABLEA
INNER JOIN TABLEB 

ON TABLEA.NAME = TABLEB.NAME ;

ID	Name		ID	Name
1	pardeep		2	pardeep
4	latte		4	latte

----------------------------------------------------------------------------------------------------------------


LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

SQL LEFT JOIN Syntax

Example 1

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Example 1

SELECT * FROM TABLEA

LEFT OUTER JOIN TABLEB

ON TABLEA.NAME = TABLEB.NAME;

ID	Name		ID	Name
1	pardeep		2	pardeep
4	latte		4	latte
2	kumar
3	java

Special case Example 2 :

Example 2

SELECT * FROM TABLEA
LEFT OUTER JOIN TABLEB
ON TABLEA.NAME = TABLEB.NAME
WHERE TABLEB.ID IS NULL;

ID	Name		ID	Name
2	kumar
3	java

--------------------------------------------------------------------------------

RIGHT JOIN The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SQL RIGHT JOIN Syntax

Example 3

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
or:

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Example 3
SELECT * FROM TABLEA
RIGHT OUTER JOIN TABLEBON TABLEA.NAME = TABLEB.NAME;

ID	Name		ID	Name
1	pardeep		2	pardeep
4	latte		4	latte
			1	oracle
			3	linkedin

Special case Example 4

Example 4

SELECT * FROM TABLEA
RIGHT OUTER JOIN TABLEB
ON TABLEA.NAME = TABLEB.NAME
WHERE TABLEA.ID IS NULL;

ID	Name		ID	Name
			1	oracle
			3	linkedin

--------------------------------------------------------------------------------


FULL OUTER JOIN
The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2).

The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins.

SQL FULL OUTER JOIN Syntax

Example 5

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;






Example 5SELECT * FROM TABLEA
FULL OUTER JOIN TABLEB
ON TABLEA.NAME = TABLEB.NAME;

Column1	Column2		ID	Name
ID	Name		1	oracle
			2	pardeep
1	pardeep		3	linkedin
			4	latte
4	latte
2	kumar
3	java

Special Case Example 6

Example 6

SELECT * FROM TABLEA
FULL OUTER JOIN TABLEB
ON TABLEA.NAME = TABLEB.NAME
WHERE TABLEA.ID IS NULL 
OR TABLEB.ID IS NULL;

ID	Name		ID	Name
			1	oracle
			3	linkedin
2	kumar
3	java

Special Case
Let consider another table Table C

ID	Name
1	pardeep
2	linkedin
3	java
4	blog

Query to extract that are common in all the tables

SELECT * FROM TABLEA INNER JOIN TABLEB

ON TABLEA.NAME=TABLEB.NAME

INNER JOIN TABLEC

ON TABLEC.NAME=TABLEB.NAME OR TABLEC.NAME=TABLEA.NAME;

NATURAL JOIN
The natural join is implemented using three possible join clauses that use the following keywords in different combinations: NATURAL JOIN, USING, and ON.

When the source and target tables share identically named columns, it is possible to perform a natural join between them without specifying a join column.The NATURAL JOIN keywords instruct Oracle to identify columns with identical names between the source and target tables. Thereafter, a join is implicitly performed between them

The general syntax for the NATURAL JOIN clause is as follows:

SELECT table1.column, table2.column
FROM table1
NATURAL JOIN table2;
The Natural JOIN USING Clause
The format of the syntax for the natural JOIN USING clause is as follows:

SELECT table1.column, table2.column
FROM table1JOIN table2 USING (join_column1, join_column2…);

While the pure natural join contains the NATURAL keyword in its syntax, theJOIN…USING syntax does not. An error is raised if the keywords NATURAL and USING occur in the same join clause. The JOIN…USING clause allows one or more equijoin columns to be explicitly specified in brackets after the USING keyword.

The Natural JOIN ON Clause
The format of the syntax for the natural JOIN ON clause is as follows:

SELECT table1.column, table2.column
FROM table1JOIN table2 ON (table1.column_name = table2.column_name);

Note:
The pure natural join and the JOIN…USING clauses depend on join columns with identical column names. The JOIN…ON clause allows the explicit specification of join columns, regardless of their column names




if you find this information useful, please comment.