Joins in SQL Server

These are mainly used for retrieving information from more than one table. There are three types of joins:

1. Inner Join.
2. Outer Join.
3. Cross Join.

Inner Join:

Extracts only matching records when the condition is true. An inner join has an ON clause for specifying the conditions. The keyword inner is optional because a JOIN clause will be INNER by default.

Syntax:

SELECT 
  [colnames|*] 
FROM [table1] 
JOIN [table2] 
  ON [condn]

Example:

SELECT 
  * 
FROM emp e 
JOIN dept d 
ON e.deptno=d.deptno

In the above example, the matching records that are in both emp and dept tables are selected.

Outer Join:

Though the condition is false, matching records will be displayed.i.e., if we want to include data rows in the result set that does not have a match in the joined table, we can create a join. When you create a outer join, the order in which the SQL statement appears is significant. The first table becomes Left Table. The second table becomes Right Table. There are three types of outer join:

Left Outer Join:

Choose the first table to create left outer join. Displays all records from left table and the matching records from the right table.

Example:

SELECT
    e.eid
  , d.deptno
FROM emp e
LEFT OUTER JOIN dept d
  ON e.deptno=d.deptno

Displays all records from emp and matching records from the dept table. If there is no matching deptno record for eid, then the row contains a NULL value in the deptno column.

Right Outer Join:

Choose the second table to create right outer join. Displays all records from right table and matching records from left table. The following example, displays all records from dept and matching records from the emp table. If there is no matching eid record for deptno, then the row contains a NULL value in the eid column.

Example:

SELECT
    e.eid
  , d.deptno
FROM emp e
RIGHT OUTER JOIN dept d
  ON e.deptno=d.deptno

Full Outer Join:

Choose the both tables to create full outer join. Displays matching and un matching records from both the tables.

Example:

SELECT
    e.eid
  , d.deptno
FROM emp e
FULL OUTER JOIN dept d
  ON e.deptno=d.deptno

Cross Join:

A cross join does not have any WHERE clause, produces the Cartesian product of the tables that are involved in the joins. The size of the Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

Example:

SELECT
    e.eid
  , d.deptno
FROM emp e
CROSS JOIN dept d
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: