Describe SQL joins with example?

There are many types of join in sql server-

INNER JOIN

This join returns rows when there is at least one match in both the tables.
See Example-

961-874-Untitled.png
Figure 1. 961-874-Untitled.png

select * from table1 inner join table2 on table1.column_name=table2.column_name
or
select * from table1 as t1 left join table2 as t2 on t1.column_name=t2.column_name



/
Figure 2.

OUTER JOIN

There are three different Outer Join methods.


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.
See Example-

961-14-LEFTOUTER.png
Figure 3. 961-14-LEFTOUTER.png

select * from table1 left outer join table2 on table1.column=table2.column
or
select * from table1 as t1 left outer join table2 as t2 on t1.column_name=t2.column_name

RIGHT OUTER JOIN

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

961-380-RIGHTOUTER.png
Figure 4. 961-380-RIGHTOUTER.png

select * from table1 right outer join table2 on table1.column=table2.column
or
select * from table1 as t1 right outer join table2 as t2 on t1.column_name=t2.column_name

FULL OUTER JOIN

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

961-658-FULL.png
Figure 5. 961-658-FULL.png

select * from table1 full outer join table2 on table1.column=table2.column

or
select * from table1 as t1 full outer join table2 as t2 on t1.column_name=t2.column_name

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.
See Example-

select * from table1 cross join table2 on table1.id=table2.id
or
select * from table1 as t1 cross join table2 as t2 on t1.column_name=t2.column_name


Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*