SQL Join Commands
The SQL Joins clause is used to combine records from two or
more tables in a database. A JOIN is a means for combining
fields from two tables by using values common to each
Consider the following two tables.
Here, it is noticeable that the join is performed in the WHERE
clause. Several operators can be used to join tables, such as =,
<, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT; they can all be
used to join tables. However, the most common operator is the
equal to symbol.
Table 1 – CUSTOMER
Table 2 – ORDERS
Now, let us join these two tables in our SELECT statement as shown
below.
SQL> SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMER, ORDERS
WHERE [Link] = ORDERS.CUST_ID;
This would produce the following result.
Types of joins in SQL
1. INNER JOIN (EQUI JOIN)
2. LEFT JOIN
3. RIGHT JOIN
4. NAURAL JOIN
5. SELF JOIN
6. CARTESIAN PRODUCT JOIN
INNER JOIN (EQUI JOIN):
The INNER JOIN creates result table by combining column
values of two tables (table1 and table2) based upon the
join-condition. The query compares each row of table1 with
each row of table2 to find all pairs of rows which satisfy the
join-condition. When the join-condition is satisfied,
column values for each matched pair of rows of table A and
table B are combined into a result row.
Syntax
SELECT table1.column1, table2.column2...
FROM table1 INNER JOIN table2
ON table1.common_field = table2.common_field;
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 the right
table; the join will still return a row in the result, but with
NULL in each column from the 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 Condition.
Syntax
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
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 the left
table; the join will still return a row in the result, but with
NULL in each column from the 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 Condition.
Syntax
SELECT table1.column1, table2.column2...
FROM table1 RIGHT JOIN table2
ON table1.common_field = table2.common_field;
SELF JOIN:
The SQL SELF JOIN is used to join a table to itself as if the
table were two tables; temporarily renaming at least one
table in the SQL statement.
Syntax:
SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;
CARTESIAN JOIN:
The CARTESIAN JOIN or CROSS JOIN returns the
Cartesian product of the sets of records from two or
more joined tables. Thus, it equates to an inner join
where the join-condition always evaluates to either
True or where the join-condition is absent from the
statement.
Syntax:
SELECT table1.column1, table2.column2...
FROM table1, table2 [, table3 ]
NATURAL JOIN:
An EQUI JOIN performs a JOIN against equality or
matching column(s) values of the associated tables and an
equal sign (=) is used as comparison operator in the where
clause to refer equality.
The SQL NATURAL JOIN is a type of EQUI JOIN and is
structured in such a way that, columns with the same name
of associated tables will appear once only.
Special conditions of Natural Join:
The associated tables have one or more pairs of
identically named columns.
The columns must be the same data type.
Don’t use ON clause in a natural join.
Syntax:
SELECT * FROM table1 NATURAL JOIN table2;
Other Syntax:
SELECT * FROM table1, table2;
The Above same result can also be produce by using the given
below command.
SELECT * FROM CUSTOMER, ORDERS;
:: Finished ::