Defferent types Joins
INNER JOIN:
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.
Syntax:
The basic syntax of INNER JOIN is as follows:SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;
Example:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
LEFT JOIN:
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.
This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.
Syntax:
The basic syntax of LEFT JOIN is as follows:SELECT table1.column1, table2.column2... FROM table1
LEFT JOIN table2 ON table1.common_field = table2.common_field;Here given condition could be any given expression based on your requirement.
Example:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
RIGHT JOIN:
The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.
This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.
Syntax:
The basic syntax of RIGHT JOIN is as follows:SELECT table1.column1, table2.column2... FROM table1 RIGHT JOIN table2 ON table1.common_field = table2.common_field;
Example:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
CARTESIAN JOIN:
The CARTESIAN JOIN or CROSS JOIN returns the Cartesian
product of the sets of records from the two or more joined tables. Thus,
it equates to an inner join where the join-condition always evaluates
to True or where the join-condition is absent from the statement.Syntax:
The basic syntax of CARTESIAN JOIN or CROSS JOIN is as follows:SELECT table1.column1, table2.column2... FROM table1, table2 [, table3 ]
Example:
SQL> SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS, ORDERS;
Comments
Post a Comment