SQL Joins in SQL Server

A SQL join is combines records from two or more tables in a relational database by using values common to each. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) can JOIN to itself in a self-join. A programmer writes a JOIN statement to identify records for joining. If the evaluated predicate is true, the combined record is then produced in the expected format, a record set or a temporary table.

Why we used SQL Join :

1. If we want to access data more than one table with the help of a select statement.

2. If we want to combines rows of that tables in one table and one can retrieve the information with the help of a SELECT statement.

3. If we want to joining of two or more tables is only based on common field between them.

SQL INNER JOIN is a simple join is the most common type of join.

We have Two tables Employee and Department. Their Structure with data is below:

SQL Inner Join:

A Simpler join is a SQL Inner Join Syntax is as follows:

SQL Outer Join:

In the SQL outer JOIN all the content of the both tables are integrated together either they are matched or not.

Outer join of two types:

1.Left outer join ( Left Join): this join returns all the rows from left table combine with the matching rows of the right table. If you get no matching in the right table it returns NULL values.

2.Right outer join (Right Join): this join returns all the rows from right table are combined with the matching rows of left table .If you get no column matching in the left table .it returns null value.

1.Left outer join ( Left Join):

Right outer join (Right Join):

SQL FULL JOIN:

SQL full outer join and SQL join are same.

The SQL full join is the result of combination of both left and right outer join and the join tables have all the records from both tables. It puts NULL on the place of matches not found. Generally it is known as SQL FULL JOIN.

SQL CROSS JOIN:

When we want to combine each row of both two tables the this types join is known as Cartesian join or cross join. In general words we can say that SQL CROSS JOIN returns the Cartesian product of the sets of rows from the joined table. We can define SQL cross join in two ways :

1.Using the JOIN syntax.

2.the table in the FROM clause without using a WHERE clause.

articles

SQL Joins in SQL Server

Anupam Mishra

Leave Comment

Comments

Liked By