articles

JOINS in SQL Server

Anonymous User 8273 06-Jul-2011

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how Microsoft SQL Server should use data from one table to select the rows in another table. SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. JOIN keyword used in an SQL statement to query data more than one table.

There are many types of joins in SQL server in which some important joins are described briefly below.

  •          INNER JOIN
  •          OUTER JOIN
  •          CROSS JOIN
INNER JOIN:

Inner joins returns rows when at least one match are found in both tables. That is we can say that inner join returns intersection value (common column data value) of two tables.

Syntax: Using SELECT command with INNER JOIN

SELECT [COLUMN_NAME] || [ALL] FROM <TABLE_NAME_ 1> <TABLE_NAME_ALIAS>

INNER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <condition to match common column name>

Example:

TABLE1: STUDENT_DETAIL

JOINS in SQL Server

TABLE2: STUDENT_DETAILS2

JOINS in SQL Server

---- SELECT DATA FROM TWO TABLE WHERE TABLE ID MATCHES
SELECT * FROM STUDENT_DETAIL STD1 INNER JOIN STUDENT_DETAILS2 STD2 ON STD1.id = STD2.ID
Desired Output:

JOINS in SQL Server

OUTER JOIN:

Outer join has three main components which are namely as follows:

  •    LEFT OUTER JOIN
  •    RIGHT OUTER JOIN
  •    FULL OUTER JOIN
LEFT OUTER JOIN:

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

Syntax: USING SELECT COMMAND WITH LEFTOUTER JOIN

SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM <TABLE_NAME_1> <TABLE_NAME_1_ALIAS>

LEFT OUTER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <CODITION MATCH COLUMN OF TWO TABLE >

Example:
---- SELECT ALL DATA FROM FIRST TABLE AS WELL AS FROM SECOND  TABLE WHERE TABLE ID MATCHES
SELECT * FROM STUDENT_DETAIL STD1 LEFT OUTER JOIN STUDENT_DETAILS2 STD2 ON STD1.id = STD2.ID
Desired Output:

JOINS in SQL Server

RIGHT OUTER JOIN:

This join returns all the rows from the right table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

 
Syntax:USING SELECT COMMAND WITH RIGHT OUTER JOIN
                SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM <TABLE_NAME_1> <TABLE_NAME_1_ALIAS>
RIGHT OUTER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <CODITION MATCH COLUMN OF TWO TABLE >
Example:
---- SELECT ALL DATA FROM SECOND TABLE AS WELL AS FROM FIRST  TABLE WHERE TABLE ID MATCHES
SELECT * FROM STUDENT_DETAIL STD1 RIGHT OUTER JOIN STUDENT_DETAILS2 STD2 ON STD1.id = STD2.ID
Desired Output:

JOINS in SQL Server

 

FULL OUTER JOIN:

This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.

Syntax: USING SELECT COMMAND WITH FULL OUTER JOIN

SELECT [COLUMN_NAME] | [ALL_COLUMN] FROM <TABLE_NAME_1> <TABLE_NAME_1_ALIAS>

FULL OUTER JOIN <TABLE_NAME_2> <TABLE_NAME_2_ALIAS> ON <CODITION MATCH COLUMN OF TWO TABLE >

Example:
---- SELECT ALL DATA FROM TWO TABLE IF ID NOT MATCH THEN RETURN NULL VALUE
SELECT * FROM STUDENT_DETAIL STD1 FULL OUTER JOIN STUDENT_DETAILS2 STD2 ON STD1.id = STD2.ID

JOINS in SQL Server

CROSS JOIN:

This join is a Cartesian join that does not necessitate any condition to join. The result set contains records that are multiplication of record number from both the tables. That is every element of column of first table is associated with every element of column of second table.

Syntax: SELECT COMMAND USING WITH CROSS JOIN

SELECT [COLUMN NAME | ALL COLUMN] FROM <FIRST_TABLENAME> <FIRST_TABLENAME_ALIAS>

CROSS JOIN <SECOND_TABLENAME> <SECOND_TABLENAME_ALIAS> ON <SQL CLAUSE|KEYWORD|CONDITION>

Example:
---- SELECT ALL CROSSALY JOIN DATA ASSOCIATED TO EACH OTHER
SELECT * FROM STUDENT_DETAIL STD1 CROSS JOIN STUDENT_DETAILS2 STD2

JOINS in SQL Server

CORSS JOIN WITH WHERE CLAUSE:
---- CROSS JOIN WITH WHERE CLAUSE
SELECT * FROM STUDENT_DETAIL STD1 CROSS JOIN STUDENT_DETAILS2 STD2 WHERE STD1.ID = STD2.ID

JOINS in SQL Server

CROSS JOIN WITH WHERE CLAUSE TO SEARCH NAME
---- CROSS JOIN WITH WHERE CLAUSE AND SEARCH PATTERN
SELECT * FROM STUDENT_DETAIL STD1 CROSS JOIN STUDENT_DETAILS2 STD2 WHERE STD1.name LIKE 'AR%'

JOINS in SQL Server

NOTE:

A WHERE clause only turns a Cross Join into an Inner Join when it provides the join criteria, as in:
WHERE TableA.Key = TableB.Key



Updated 07-Sep-2019
I am a content writter !

Leave Comment

Comments

Liked By