Join query for SQL Server Data Query

Document directory

• Left join keyword syntax
• Right join keyword syntax

4. join query

1. Join

Join in 1.1 (inner join)

 

1.2 Outer Join)

1.2.1 left join)

1.2.2 right join)

 

1.3 cross join)

 

2. Inner join and self join

Inner join: Act on two tables. Generally, the contact is established through the relationship between the two tables with the same fields (=/<> ).

Self-join: two instances acting on their own tables

 

3. Outer Join

1. Left join

SQL left join keyword

The left join keyword returns all rows from the left table (table_name1), even if no matching row exists in the right table (table_name2.

Left join keyword syntax

SELECT column_name(s)FROM table_name1LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

Note: In some databases, left join is called left Outer Join.

Original table (used in the example ):

"Persons" table:

Id_p	Lastname	Firstname	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Th Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" table:

Id_o	Orderno	Id_p
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

Left join instance

Now we want to list all people and their orders-if any.

You can use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsLEFT JOIN OrdersON Persons.Id_P=Orders.Id_PORDER BY Persons.LastName

Result set:

Lastname	Firstname	Orderno
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
Bush	George

The left join keyword returns all rows from the left table (persons), even if there is no matching

 

2. Right join

SQL right join keyword

The right join keyword returns all rows from the right table (table_name2), even if no matching row exists in the left table (table_name1.

Right join keyword syntax

SELECT column_name(s)FROM table_name1RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

Note: In some databases, right join is called right outer join.

Original table (used in the example ):

"Persons" table:

Id_p	Lastname	Firstname	Address	City
1	Adams	John	Oxford Street	London
2	Bush	George	Th Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

"Orders" table:

Id_o	Orderno	Id_p
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	65

Right join instance

Now, we want to list all the orders and the persons who ordered them-if any.

You can use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNoFROM PersonsRIGHT JOIN OrdersON Persons.Id_P=Orders.Id_PORDER BY Persons.LastName

Result set:

Lastname	Firstname	Orderno
Adams	John	22456
Adams	John	24562
Carter	Thomas	77895
Carter	Thomas	44678
		34764

The right join keyword returns all rows from the right table (orders), even if no matching row exists in the left table (persons.

 

3. Cross join)