Join
The JOIN operator allows you to combine multiple related tables to retrieve data based on the logical relationship between the tables.
SYNTAX:
SELECT *
FROM TABLE1 (JOIN_TYPE) TABLE2 ON (JOIN_CONDITION)
JOIN_TYPE: There are five types of JOIN which are ANSI-compliant: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN and CROSS JOIN.
JOIN_CONDITION: Join condition consists of column from each of the tables involved in the JOIN along with a comparison operator to compare the two columns.
Even though it is not required to have a primary key and foreign key relationship between the columns involved in the JOIN, but both the column needs to contain similar data for the results to be meaningful.
When tables involved in the JOIN operator contains same column name, it is required to qualify the column with the table name.
Categories: Join can be divided into 3 categories. Inner JOIN, Outer JOIN and Cross JOIN.
a) INNER JOIN: Returns back only those records which are common to both the tables involved in the JOIN.
b) OUTER JOIN: Returns all rows from one table and only common rows from the other table, or it can return all the rows from both the tables. There are three Outer Joins available. Left Outer JOIN, Right Outer JOIN and Full Outer JOIN.
c) CROSS JOIN: Returns back Cartesian product of records from both the tables involved in the JOIN.
b) OUTER JOIN: Returns all rows from one table and only common rows from the other table, or it can return all the rows from both the tables. There are three Outer Joins available. Left Outer JOIN, Right Outer JOIN and Full Outer JOIN.
c) CROSS JOIN: Returns back Cartesian product of records from both the tables involved in the JOIN.
