Working with 'INNER JOIN'
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int ) 5> 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> 3> insert into job(ID, title, averageSalary) values(1,'Developer',3000) 4> 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> select * from employee; 3> 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> 3> SELECT e.ID, e.Name, j.title 4> FROM Employee e INNER JOIN Job j 5> ON e.Id = j.ID 6> GO ID Name title ----------- ---------- ---------- 1 Jason Developer 2 Robert Tester 3 Celia Designer 4 Linda Programmer (4 rows affected) 1> 2> 3> 4> drop table employee; 5> drop table job; 6> GO 1>
1. | 'INNER JOIN' with different syntax | ||
2. | Using the * operator to reference entire row | ||
3. | Inner join with condition on joined field |