Thursday, July 11, 2013

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.

1 comment:

  1. For latest and updated Oracle certification dumps in PDF format contact us at completeexamcollection@gmail.com.
    Refer our blog for more details http://completeexamcollection.blogspot.in/

    ReplyDelete