SQL Server/T-SQL/Table Joins/Left Join
LEFT JOIN with group by and order by
1>
2> create table employee(
3> ID int,
4> name nvarchar (10),
5> salary int )
6> GO
1>
2> create table job(
3> ID int,
4> title nvarchar (10),
5> averageSalary int)
6> GO
1>
2>
3> insert into employee (ID, name, salary) values (1, "Jason", 1234)
4> GO
1> insert into employee (ID, name, salary) values (2, "Robert", 4321)
2> GO
1> insert into employee (ID, name, salary) values (3, "Celia", 5432)
2> GO
1> insert into employee (ID, name, salary) values (4, "Linda", 3456)
2> GO
1> insert into employee (ID, name, salary) values (5, "David", 7654)
2> GO
1> insert into employee (ID, name, salary) values (6, "James", 4567)
2> GO
1> insert into employee (ID, name, salary) values (7, "Alison", 8744)
2> GO
1> insert into employee (ID, name, salary) values (8, "Chris", 9875)
2> GO
1> insert into employee (ID, name, salary) values (9, "Mary", 2345)
2> GO
1>
2> insert into job(ID, title, averageSalary) values(1,"Developer",3000)
3> GO
1> insert into job(ID, title, averageSalary) values(2,"Tester", 4000)
2> GO
1> insert into job(ID, title, averageSalary) values(3,"Designer", 5000)
2> GO
1> insert into job(ID, title, averageSalary) values(4,"Programmer", 6000)
2> GO
1>
2>
3> select * from employee;
4> GO
ID name salary
----------- ---------- -----------
1 Jason 1234
2 Robert 4321
3 Celia 5432
4 Linda 3456
5 David 7654
6 James 4567
7 Alison 8744
8 Chris 9875
9 Mary 2345
1> select * from job;
2> GO
ID title averageSalary
----------- ---------- -------------
1 Developer 3000
2 Tester 4000
3 Designer 5000
4 Programmer 6000
1>
2> SELECT count(e.id)
3> FROM Employee e LEFT JOIN job j
4> ON e.ID = j.ID
5> group by j.ID
6> ORDER BY j.ID
7> GO
-----------
5
1
1
1
1
1>
2>
3>
4> drop table employee;
5> drop table job;
6> GO
1>
2>
Using TOP in left join
1>
2> create table employee(
3> ID int,
4> name nvarchar (10),
5> salary int )
6> GO
1>
2> create table job(
3> ID int,
4> title nvarchar (10),
5> averageSalary int)
6> GO
1>
2>
3> insert into employee (ID, name, salary) values (1, "Jason", 1234)
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (2, "Robert", 4321)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (3, "Celia", 5432)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (4, "Linda", 3456)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (5, "David", 7654)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (6, "James", 4567)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (7, "Alison", 8744)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (8, "Chris", 9875)
2> GO
(1 rows affected)
1> insert into employee (ID, name, salary) values (9, "Mary", 2345)
2> GO
(1 rows affected)
1>
2> insert into job(ID, title, averageSalary) values(1,"Developer",3000)
3> GO
(1 rows affected)
1> insert into job(ID, title, averageSalary) values(2,"Tester", 4000)
2> GO
(1 rows affected)
1> insert into job(ID, title, averageSalary) values(3,"Designer", 5000)
2> GO
(1 rows affected)
1> insert into job(ID, title, averageSalary) values(4,"Programmer", 6000)
2> GO
(1 rows affected)
1>
2>
3> select * from employee;
4> GO
ID name salary
----------- ---------- -----------
1 Jason 1234
2 Robert 4321
3 Celia 5432
4 Linda 3456
5 David 7654
6 James 4567
7 Alison 8744
8 Chris 9875
9 Mary 2345
(9 rows affected)
1> select * from job;
2> GO
ID title averageSalary
----------- ---------- -------------
1 Developer 3000
2 Tester 4000
3 Designer 5000
4 Programmer 6000
(4 rows affected)
1>
2> SELECT TOP 5 e.ID, e.Name , j.title
3> FROM Employee e LEFT JOIN job j
4> ON e.ID = j.ID
5> ORDER BY j.ID
6> GO
ID Name title
----------- ---------- ----------
6 James NULL
5 David NULL
7 Alison NULL
8 Chris NULL
9 Mary NULL
(5 rows affected)
1>
2> drop table employee;
3> drop table job;
4> GO
1>
2>