module 08 querying multiple tables

8/9/2019 Module 08 Querying Multiple Tables

1/37

Module 8:Querying MultipleTables

Vidya Vrat Agarwal. | MCT, MCSD


2/37

Overview

Introduction to Joins

Using Inner Joins

Using Outer Joins

Using Cross Joins

Joining More Than Two Tables

Joining a Table to Itself

Natural Join Equi, Non-Equi and Theta Join

Union


3/37


A Join is an Operation that allows to Query Two or More tablesto produce a result set. That Incorporates rows and columnsfrom each table.

Joins Operation is used on Columns that are common and ofSame Data Type to both tables.

The process of forming pairs of rows by matching the contentsof related columns is called Joining the Tables.


4/37

Purpose of Joins

A Join:

Selects Specific Columns from Multiple Tables

JOIN keyword specifies that tables are joined and how tojoin them

ON keyword specifies join condition

Queries Two or More Tables to Produce a Result Set

Use Primary and Foreign keys as join conditions

Use columns common to specified tables to join tables


5/37

Inner Join

Inner Join combine rows by comparing values in columnsthat are common to both tables.

i.e. UnMatched rows are not included in the result set.

1

4

7

8

12

2

4

5

8

13


6/37

Using Inner Joins

USE joindb

SELECT buyer_name, sales.buyer_id, qty

FROM buyers INNER JOIN sales

ON buyers.buyer_id = sales.buyer_id

USE joindb


FROM buyers INNER JOIN sales


sales

buyer_id

buyer_idbuyer_id prod_id

prod_idprod_id qty

qtyqty

1

1

1

1

4

4

3

3

2

2

3

3

1

1

5

5

15

15

5

5

37

37

11

11

4

4 2

2 1003

1003

buyers

buyer_name

buyer_namebuyer_name

Adam Barr

Adam Barr

Sean Chai

Sean Chai

Eva Corets

Eva Corets

Erin OMelia

Erin OMelia

buyer_id

buyer_idbuyer_id

1

1

2

2

3

3

4

4

Result

buyer_name

buyer_namebuyer_name

Adam Barr

Adam Barr

Adam Barr

Adam Barr

Erin OMelia

Erin OMelia

Eva Corets

Eva Corets

buyer_id

buyer_idbuyer_id

qty

qtyqty

1

1

1

1

4

4

3

3

15

15

5

5

37

37

11

11

Erin OMelia

Erin OMelia 4

4 1003

1003

3

4

1

1

4

1

2

3

4

1

1

4

3

4


7/37

Outer Join

Outer join retains UnMatched rows in result set.

Following are the 3 Types of Outer Joins :

1. Left Outer Join

2. Right Outer Join

3. Full Outer Join


8/37

Left and Right Outer Join

Left Outer Join combine rows from Left Table, plus anyUnMatched rows from the Right Table.

Right Outer Join combine rows from Right Table, plus any

UnMatched rows from the Left Table.

Rows that do not match the Join Condition, display NULLin result set.

The only difference between a Right and Left Outer Join is,how the Query is written. i.e., the Position of tables.


9/37

Using Outer Joins

USE joindb


FROM buyers LEFT OUTER JOIN sales


USE joindb


FROM buyers LEFT OUTER JOIN sales


sales

buyer_idbuyer_idbuyer_id prod_idprod_idprod_id qtyqtyqty

11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

buyers

buyer_namebuyer_namebuyer_name

Adam BarrAdam Barr

Sean ChaiSean Chai

Eva CoretsEva Corets

Erin OMeliaErin OMelia

buyer_idbuyer_idbuyer_id

11

22

33

44 3

4

1

1

4

1

2

3

4Result


Adam BarrAdam Barr

Adam BarrAdam Barr



buyer_idbuyer_idbuyer_id qtyqtyqty

11

11

44

33

1515

55

3737

1111

Erin OMeliaErin OMelia 44 10031003

Sean ChaiSean Chai NULLNULL NULLNULL

11

4

3

4

NULL


10/37

Full Outer Join

A Full Outer Join statement retrieves all records from bothsides of the relationship.

For any record on either side that has no matching recordNULL is displayed.


11/37

Cross Join

A Cross Join produces the combination of all rows in thejoined tables.

When this occurs, every possible relationship between thetwo table is executed.

The Output of such join is called a Cartesian Product.

Cross Join can be successfully used for generating large

amounts of sample data for testing databaseperformance.


12/37

Using Cross Joins

USE joindb

SELECT buyer_name, qty

FROM buyers CROSS JOIN sales

USE joindb


FROM buyers CROSS JOIN sales

Result


Adam BarrAdam Barr

Adam BarrAdam Barr

Adam BarrAdam Barr

Adam BarrAdam Barr

qtyqtyqty

1515

55

3737

1111

Adam BarrAdam Barr 10031003

Sean ChaiSean Chai 1515





Eva CoretsEva Corets 1515

Eva CoretsEva Corets 55

...... ......

Adam Barr

Adam BarrAdam Barr

Adam Barr

15

537

11

Adam Barr 1003

sales

buyer_idbuyer_idbuyer_id prod_idprod_idprod_id qtyqtyqty

11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

1

14

3

2

31

5

15

537

11

4 2 1003

1

14

3

2

31

5

15

537

11

4 2 1003

buyers


11

22

33

44


Adam BarrAdam Barr

Sean ChaiSean Chai



1 Adam Barr

USE joindb


FROM buyers, sales

USE joindbSELECT buyer_name, qty

FROM buyers, sales


13/37

Joining More Than Two Tables

SELECT buyer_name, prod_name, qty

FROM buyers JOIN salesON buyers.buyer_id = sales.buyer_id

JOIN produce

ON sales.prod_id = produce.prod_id

SELECT buyer_name, prod_name, qty

FROM buyers JOIN salesON buyers.buyer_id = sales.buyer_id

JOIN produce

ON sales.prod_id = produce.prod_id

produce

prod_idprod_idprod_id prod_nameprod_nameprod_name

1122

33

44

ApplesApplesPearsPears

OrangesOranges

BananasBananas

55 PeachesPeaches

buyers


1122

33

44


Adam BarrAdam BarrSean ChaiSean Chai



sales


1111

33

44

prod_idprod_idprod_id

2233

11

55

22 22

qtyqtyqty

151555

3737

1111

10031003

12

3

4

11

4

3

4

23

1

5

2

12

3

4

5Result



Adam BarrAdam Barr


Adam BarrAdam Barr


prod_nameprod_nameprod_name

ApplesApples

PearsPears

PearsPears

OrangesOranges

PeachesPeaches

qtyqtyqty

3737

1515

10031003

55

1111


14/37

Self Join Joining a Table to Itself

If a Table is joined to itself, is called as Self join.

It compares values with a column of a single table.

The following could be the Situations for Self Joins :

Find out the employees who have same Joining date.

Find the largest or smallest value available.


15/37


USE joindbSELECT a.buyer_id AS buyer1, a.prod_id,

b.buyer_id AS buyer2FROM sales a JOIN sales bON a.prod_id = b.prod_id

WHERE a.buyer_id > b.buyer_id

USE joindbSELECT a.buyer_id AS buyer1, a.prod_id,

b.buyer_id AS buyer2FROM sales a JOIN sales bON a.prod_id = b.prod_id

WHERE a.buyer_id > b.buyer_idsales b

buyer_idbuyer_idbuyer_id prod_idprod_idprod_id qtyqtyqty11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

sales a

buyer_idbuyer_idbuyer_id prod_idprod_idprod_id qtyqtyqty11

11

44

33

22

33

11

55

1515

55

3737

1111

44 22 10031003

2

3

1

5

2

2

3

1

5

2

Result

buyer1buyer1buyer1

44

prod_idprod_idprod_id buyer2buyer2buyer2

22 11


16/37

Natural Join

A join that restricts the Redundant Column data from theresult set is known as Natural Join.

If one of these columns is eliminated, then join is calledthe Natural Join.

Select c.Course_name,s.Student_ID,s.Student_name

From Course c,Student s, Registration r

Where s.student_id Like '%[125]' And

s.student_id=r.Student_ID

Student_Id of Registration table is not used because it iscommon to both the tables ( student and Registration).


17/37

Equi, Non-Equi and Theta Joins

If the Comparison Operator is Equality(=) then the join is calledan Equi Join.

If the Comparison Operator is not the equal sign then it is aNon-Equi Join.

SELECT p.pub_name, p.state, a.au_lname, a.au_fname, a.state

FROM publishers p INNER JOIN authors a ON a.state > p.state

A join based on any other comparison operator

( < ,>,=,) is a Theta ( ) Join.


18/37

Union

The UNION operator allows you to combine the resultsof two or more set of rows into a single result set.

5mno3ghi

4jkl2def

3ghi1abc

--------------------

ColumnDColumnCColumnBColumnA

Table2Table1

Whereas, a Join combines two sets of columns into

a single result set.


19/37

SELECT * FROM Table1

UNION [ALL]

SELECT * FROM Table2

Result Set :

ColumnA ColumnB

------- --------

abc 1

def 2

ghi 3

Jkl 4

Mno 5


20/37

Check Your Understanding.


21/37

Q.1. ______________ is a query in which data is retrievedfrom more than one table.

1. Union2. Join

3. Subquery

4. None


22/37

Q.2. The Column names in the select list need not

to be qualified with their table names if thereare no columns with the same name in bothtables.

1. True

2. False


23/37

Q.3. When the comparison operator of a join is equality,hat join is called ?

1. Equality Join

2. Equi join

3. Natural join4. Outer join


24/37

Q.4. What is the result of the following query .?Select emp.*,dept.*

From emp,dept

Where emp.deptid=dept.deptid

1. All columns from emp table

2. All columns from dept table

3. All columns from emp and dept tables.

4. None of the above.


25/37

Q.5. What is Natural Join.?


26/37

Q.6. The following is an example of a natural join sincethe column Deptid of the Department table is omittedfrom the select list.?

Select E.empno,E.name,E.deptid,D.name,D.manager

From employee E, department DWhere E.deptid= D.deptid

1. True

2. False


27/37

Q.7. What is Self Join .?


28/37

Q.8. The following is an example of a self join.?

Select distinct E1.name,E1.dt_join

From Employee E1, Employee E2

Where E1.dt_join=e2.dt_join

1. True

2. False


29/37

Q.9. ___________ is a table that contains all thepossible combinations of rows.?

1. Result set

2. Combination set

3. Combination Product4. Cartesian Product


30/37

Q.10. The following query will result in a cartesianproduct.?

Select Employee.*,Salary.*

From Employee, Salary

1. True

2. False


31/37

Q.11. What is Outer Join and what are the types.?


32/37

Q.12. What is the difference between Inner, outer andcross joins .?


33/37

Q.13.What is Union Operator.?


34/37

Q.14. For Union compatibility the two tables must containthe same number of columns.?

1. True

2. False


35/37

Q.15. What is the difference between Join and Union .?


36/37

Overview


Using Inner Joins

Using Outer Joins

Using Cross Joins Joining More Than Two Tables


Natural Join

Equi, Non-Equi and Theta Join

Union


37/37

The Journey ofThousand miles,

begin with one StepThank You.

module 08 querying multiple tables

Documents