Using Joins SQL Server Examples

SQL SERVER > QUERY > JOIN

Using joins you can retrieve data from two or more tables based on logical relationships between the tables.

create table #category(name varchar(20), description  varchar(20))

insert into #category(name,description)
values('cat 1','cat 1 description')
insert into #category(name,description)
values('cat 2','cat 2 description')
insert into #category(name,description)
values('cat 3','cat 3 description')

create table #product(id int, name varchar(20), category varchar(20))

insert into #product(id,name,category)

values(1,'product 1','cat 1')
insert into #product(id,name,category)
values(2,'product 2','cat 2')
insert into #product(id,name,category)
values(2,'product 3',null)

INNER JOIN  returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column. 

select p.id, p.name, c.description as category

from #product p
inner join #category c
       on p.category = c.name

LEFT OUTER JOIN includes all rows in the a table  whether or not there is a match on the another table.

select p.id, p.name, c.description as category

from #product p
left  join #category c
       on p.category = c.name

RIGHT OUTER JOIN indicates all rows in the second table are to be included in the results, regardless of whether there is matching data in the first table.

select p.id, p.name, c.description as category

from #product p
right  join #category c
       on p.category = c.name

FULL OUTER JOIN includes all rows from both tables, regardless of whether or not the other table has a matching value.

select p.id, p.name, c.description as category

from #product p
full  join #category c
       on p.category = c.name

CROSS JOIN produces the Cartesian product of the tables involved in the join.

 select p.id, p.name, c.description as category
from #product p
cross join #category c

      

drop table #product

drop table #category