09 - Quering data using joins

SQL server executes the SQLs and returns the result set. The result is the set of rows that are retrieved from the table. There may be a need to retrieve the data from more than one table. There must be one common attribute in the tables to perform the operation.

The connecting column from both the tables should have some common values. The values should be of similar data type.

The general syntax for join is as shown below:

Consider the two tables: studentdetails and studentresult. The tables are as shown below:

studentdetails

StudID

StudName

StudAge

201

Adam

16

202

Nick

14

203

Michael

18

204

Peter

19

205

Jerry

16

studentresult

StudID

StudMark

StudGrade

202

45

B

204

80

A

205

35

C

206

70

A

207

90

A

The join condition for the two tables is as shown below:

The output table is as shown below:

StudName

StudMark

202

45

204

80

205

35

 

9.1 Using an Inner join

The inner join command is used to combine data from more than 1 table. The records are retrieved from multiple tables after comparing the common column. When the inner join is applied, only the rows with values satisfying the join condition are displayed.

Consider the tables:  empstatus and salary.

empstatus

Empid

Birthdate

Hiredate

301

1980-01-02

2010-04-06

320

1978-01-01

2011-01-03

322

1976-01-02

2013-07-05

328

1982-01-02

2010-06-03

321

1984-01-02

2010-10-04

341

1988-01-02

2010-09-11

101

1987-05-08

2010-02-02

102

1987-07-08

2010-02-03

 

salary

Empid

salary

role

101

14000

developer

102

40000

Lead

103

50000

Analyst

104

25000

developer

105

100000

Unit head

106

25000

developer

107

40000

Lead

 

The query to display the data using the inner join is as shown below:

The output for the join query is as shown below:

empid

Birthdate

salary

101

1987-05-08

14000

102

1987-07-08

40000

 

9.2 Using an Outer join

The outer join is used to select the data from the table irrespective of the data from the second table. The result set is containing all the rows from one table and the matching rows from the other table.

The syntax for the outer join is as shown below:

The outer join is of the following types:

1) Left Outer Join

2) Right Outer Join

3) Full Outer Join

 

1) Left Outer Join

The left outer join returns the rows from the table specified on the left side of the LEFT OUTER JOIN keyword and all the matching rows from the specified table on the right side. It displays the NULL for the columns of the table specified on the right side when there are no matching records.

Consider the tables: empstatus and empholiday.

empstatus

Empid

Birthdate

Hiredate

301

1980-01-02

2010-04-06

320

1978-01-01

2011-01-03

322

1976-01-02

2013-07-05

328

1982-01-02

2010-06-03

321

1984-01-02

2010-10-04

341

1988-01-02

2010-09-11

101

1987-05-08

2010-02-02

102

1987-07-08

2010-02-03

 

empholiday1

Empid

emprole

VacationHours

201

Manager

80

301

Lead

65

381

Manager

80

401

Engineer

50

550

Lead

65

332

Senior Manager

100

481

Manager

80

631

Engineer

50

320

Manager

90

344

Lead

70

328

Unit head

100

 

The query to display the left outer join is as shown below:

The output for the table is as shown below:

Empid

emprole

empid

201

Manager

NULL

301

Lead

301

381

Manager

NULL

401

Engineer

NULL

550

Lead

NULL

332

Senior Manager

NULL

481

Manager

NULL

631

Engineer

NULL

320

Manager

320

344

Lead

344

328

Unit head

328

 

2) Right Outer Join

The right outer join returns the rows from the table specified on the right hand side of the RIGHT OUTER JOIN keyword and the matching rows from the table specified on the left side.

Consider the tables: empstatus and empholiday1.

empstatus

Empid

Birthdate

Hiredate

301

1980-01-02

2010-04-06

320

1978-01-01

2011-01-03

322

1976-01-02

2013-07-05

328

1982-01-02

2010-06-03

321

1984-01-02

2010-10-04

341

1988-01-02

2010-09-11

101

1987-05-08

2010-02-02

102

1987-07-08

2010-02-03

 

empholiday1

Empid

emprole

VacationHours

201

Manager

80

301

Lead

65

381

Manager

80

401

Engineer

50

550

Lead

65

332

Senior Manager

100

481

Manager

80

631

Engineer

50

320

Manager

90

344

Lead

70

328

Unit head

100

 

Consider the following query for the right outer join.

The output of the query is as shown below:

empid

emprole

empid

301

Lead

301

320

Manager

320

NULL

NULL

322

328

Unit head

328

NULL

NULL

321

NULL

NULL

341

344

Lead

344

NULL

NULL

101

NULL

NULL

102

3) Full Outer Join

The full outer join is the combination of left and right outer join. This join returns all the matching and non matching rows from both the tables. The similar records are displayed only once. The NULL value is returned for the columns for which the data is unavailable.

 

The query for full outer join is applied to the empstatus and empholiday1 table. The sql statement is as shown below:

The output of the query is as shown below:

empid

emprole

empid

NULL

NULL

322

NULL

NULL

321

NULL

NULL

341

NULL

NULL

101

NULL

NULL

102

201

Manager

NULL

301

Lead

301

320

Manager

320

328

Unit head

328

332

Senior Manager

NULL

344

Lead

344

381

Manager

NULL

401

Engineer

NULL

481

Manager

NULL

550

Lead

NULL

631

Engineer

NULL

 

9.3 Using a cross join

 

The cross join is known as Cartesian Product. It combines each row of one table with each row of another table. The number of rows in the result set is equal to the number of rows in the first table multiplied by the number of rows in the second table.

The syntax for the cross join is as shown below:

Consider the example of Department and Employee table.

Department

DeptID

Deptname

Location

D101

Sales

New York

D301

HR

Chicago

D455

Packing

Paris

D644

Transport

LA

D690

Finance

Sydney

Employee

EmpID

EmpName

E101

Sam

E102

Henry

E103

Dick

E104

Mary

 

The cross join query for the table is as shown below:

The output for the query is as shown below:

DeptID

Deptname

Location

EmpID

EmpName

D101

Sales

NewYork

E101

Sam

D301

HR

Chicago

E101

Sam

D455

Packing

Paris

E101

Sam

D644

Transport

LA

E101

Sam

D690

Finance

Sydney

E101

Sam

D101

Sales

NewYork

E102

Henry

D301

HR

Chicago

E102

Henry

D455

Packing

Paris

E102

Henry

D644

Transport

LA

E102

Henry

D690

Finance

Sydney

E102

Henry

D101

Sales

NewYork

E103

Dick

D301

HR

Chicago

E103

Dick

D455

Packing

Paris

E103

Dick

D644

Transport

LA

E103

Dick

D690

Finance

Sydney

E103

Dick

D101

Sales

NewYork

E104

Mary

D301

HR

Chicago

E104

Mary

D455

Packing

Paris

E104

Mary

D644

Transport

LA

E104

Mary

D690

Finance

Sydney

E104

Mary

 

9.4 Using an equi join

An equi join is same as an inner join table. The equality operator is used to specify the join condition, whereas the conditional operators are used to specify the join condition in an inner join.

The syntax for the equi join is as shown below:

Consider the table as UserDetails and OrderDetials.

UserDetails

UserID

FirstName

LastName

1

Kent

Damiano

2

Fedora

Smith

3

Mischa

Potter

OrderDetails

OrderID

OrderNumber

UserID

1

65432

1

2

98743

2

3

45632

1

 

The query for displaying the equi join data is as shown below:

The output of the query is as shown below:

UserID

FirstName

LastName

OrderID

OrderNumber

UserID

1

Kent

Damiano

1

65432

1

2

Fedora

Smith

2

98743

2

3

Kent

Damiano

3

45632

1

9.5 Using a self join

The self join is a table joined with itself. One row of the table correlates with the other rows in the same table. The table name is used twice in the query. To differentiate between the two tables, the table name is given an alias name.

The syntax for the self join is as shown below:

Consider the Student table.

ID

Name

Age

Class

1

Loreal

20

Medical

2

Perry

22

Medical

3

Linda

17

Computer

4

Kats

16

Mechanics

5

Peter

21

Medical

The query for the table is as shown below:

The output of the code is as shown below:

ID

Name

Age

Class

1

Loreal

20

Medical

2

Perry

22

Medical

5

Peter

21

Medical

3

Linda

17

Computer

 

Like us on Facebook