SQL Joining:
Joins in SQL are used to query
(retrieve) data from 2 or more related tables. In general tables are related to
each other using foreign key constraints.
In SQL there are different types of JOINS:
1. CROSS JOIN
2. INNER JOIN
3. OUTER JOIN
Outer Joins are again divided into 3 types:
1. Left Join or Left Outer Join
2. Right Join or Right Outer Join
3. Full Join or Full Outer Join
General Formula for Joins
SELECT ColumnList
FROM LeftTableName
JOIN_TYPE RightTableName
ON JoinCondition
CROSS JOIN:
CROSS JOIN, produces the
Cartesian product of the 2 tables involved in the join. Cross Join shouldn't
have ON clause.
CROSS JOIN Query:
SELECT ColumnName1, ColumnName2,
ColumnName3,ColumnName4
FROM tableName
CROSS JOIN tableName2(joining
table)
JOIN or INNER JOIN Statement:
SELECT ColumnName1, ColumnName2,
ColumnName3,ColumnName4
FROM tableName
INNER JOIN tableName2
ON tableName.ColumnName1=
tableName2.ColumnName2
OR
SELECT ColumnName1, ColumnName2,
ColumnName3,ColumnName4 FROM tableName
JOIN tableName2
ON tableName.columnName1=
tableName2.columnName2
N.B: JOIN or INNER JOIN means the same. It's
always better to use INNER JOIN, as this explicitly specifies your intention.
Remember INNER JOIN, returns only
the matching rows between both the tables. Non-matching rows are eliminated.
LEFT JOIN or LEFT OUTER JOIN:
The LEFT JOIN keyword returns all
records from the left table (table1), and the matched records from the right
table (table2). The result is NULL from the right side, if there is no match.
Statement: SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON
table1.column_name = table2.column_name;
N.B: LEFT JOIN or LEFT OUTER JOIN
any one we can use. OUTER keyword is optional.
LEFT JOIN, returns all the
matching rows + non-matching rows from the left table. In reality, INNER JOIN
and LEFT JOIN are extensively used.
SQL RIGHT JOIN: The RIGHT JOIN
keyword returns all records from the right table (table2), and the matched
records from the left table (table1). The result is NULL from the left side,
when there is no match data.
Statement: SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON
table1.column_name = table2.column_name;
SQL FULL OUTER JOIN:
The FULL OUTER JOIN keyword returns
all records when there is a match in either left (table1) or right (table2)
table records except unmatched data.
Query Looks like: SELECT
column_name(s)
FROM table1
FULL OUTER JOIN table2 ON
table1.column_name = table2.column_name;
We can use both of the keywords
for full joining purpose. Full Join or Full Outer Join are same keywords. Outer
is an optional keyword.
Self-Join:
A self-JOIN is a regular join,
but the table is joined with itself.
Query seems to be: SELECT
column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
Sql Join Venn Diagram is given
below:
Comments
Post a Comment