Queries With Multiple Tables Updated

QUERIES WITH
MULTIPLE
TABLES
BY
MANIKANDAN. R
o
o
o
o
o
DEFINITION
A database usually comprises of many tables.

More than two tables can be joined in a single SQL statement.
This process is called joining one table to another.
Data is being selected from two tables and compiled once.
The main logic is in the WHERE clause.
STUDENT
NAME
MARK1
MARK3
MARK3
TOTAL
AVG
RESULT
RAM
68
90
72
230
76.66
PASS
PARTHI
95
92
80
267
89.00
PASS
PAPPU
45
60
71
176
58.66
PASS
STUDENTINFO
NAME
ADDRESS
P_NUM
DOB
RAM
CHENNAI
223344
12-6-1997
PARTHI
HOSUR
228899
14-1-1996
PAPPU
VELLORE
228855
12-1-1993
We need to combine two tables.

SELECT STUDENT.NAME, STUDENT.AVG,
STUDENT.RESULT, STUDENTINFO.P_NUM FROM
STUDENT, STUDENTINFO WHERE
STUDENT.NAME=STUDENTINFO.NAME;
RESULT
NAME
AVG
RESULT
P_NUM
RAM
76.66
PASS
223344
PARTHI
89.00
PASS
228899
PAPPU
58.66
PASS
228855
Mention two table names in FROM clause.

Select statement list the columns to be selected.
Column name is pre-fixed with the table name
Ex: student.name
It specifies that the item where the names match are to be
displayed.
SUB QUERIES
Some times we might need to write one SELECT
statement inside another that is called a nested query.
The outer SELECT is called as the outer query.

The inner SELECT is called as the inner query or sub
query.
Suppose we want a list of students whose total marks are

NAME
MARK1
MARK3
MARK3
TOTAL
AVG
RESULT
RAM
68
90
72
230
76.66
PASS
PARTHI
95
92
80
267
89.00
PASS
PAPPU
45
60
71
176
58.66
PASS
greater than the overall average.
SELECT NAME, TOTAL

FROM STUDENT
WHERE TOTAL>(SELECT AVG(TOTAL)
FROM STUDENT;
(OR)
SELECT NAME, TOTAL FROM STUDENT WHERE
TOTAL>224.33;
The inner query is executed first and exactly once.

The average of total for all students is calculated.
Next outer query is executed.
Every row of the student table the inner query is tested once.
RESULT
NAME
MARK1
MARK3
MARK3
TOTAL
AVG
RESULT
RAM
68
90
72
230
76.66
PASS
PARTHI
95
92
80
267
89.00
PASS
JOINS
Join is a query in which data is retrieved from more than one
table.
A join matches data from two or more tables based on the

common column in the tables.
Join is based on a primary key or foreign key make a good join.

SQL joins can be classified into Equi join and Non-Equi join.
SELECT EMPID, ENAME, DEPT,MANAGER
FROM EMP,DEPT
WHERE EMP.DNO=DEPT.DNO;
1.SQL EQUI JOIN

It is a simple sql join condition which uses the equal sign
as the comparison operator.
Two types of equi joins:

1.Inner Join
2.Outer Join
Inner join:
Retrieving the data form two tables.
We need to identify the common column b/w two tables.
SELECT EMPID, ENAME, DEPT,MANAGER
FROM EMP,DEPT
WHERE EMP.DNO=DEPT.DNO;
DNO is a column in both the tables and need a way to be

identified.
ALIASES:
Aliases are used to increase the readability of the sql.

The table name need not be entered each and every time.
Its can be Letter or numbers.
SELECT EMPID, ENAME, DEPT, MANAGER

FROM EMP E, DEPT D
WHERE E.DNO=D.DNO;
OUTTER JOIN:
An outer join does not require each record in the two joined tables
to have a matching record.
An outer join is an extended from ordinary (inner) join.
Not matching rows in the other table will also appear in the result
table with NULL.
1.Left outer join

2.Right outer join
3.Full outer join
LEFT OUTER JOIN:

SELECT *FROM EMP LEFT OUTER JOIN DEPT ON
EMP.DNO=DEPT.DNO ;
The result of left outer join for the table A and B always
contain all records of the left table A.
Does not find any matching records in right table B.

RIGHT OUTER JOIN:
A right outer join closely resembles a left outer join, except
with the treatment of the table reversed.
SELECT *FROM EMP RIGHT OUTER JOIN DEPT ON
EMP.DNO=DEPT.DNO;
FULL OUTER JOIN:
Combines the result of both left and right outer join.

The joined table will contain all records from both table.
Fill in NULL for missing matches on either side.
SELECT *FROM EMP FULL OUTER JOIN DEPT ON
EMP.DNO=DEPT.DNO;
NATURAL JOIN
Comparing all columns in both tables that have the same

column name in the joined table.
The resulting joined tables contains only one column for

each pair of equally named columns.
SELECT *FROM EMP NATURAL JOIN DEPT;
SQL NON EQUI JOIN

Condition is established using all comparison operators
except the equal (=) operator. (>=, <=, >, <, !=)
SELECT F_NAME, L_NAME, SUBJECT
FROM STUDENT
WHERE SUBJECT != MATHS;
UNION
Union operation combines two sets of rows.
Union by retrieving the information from the two SELECT
statements.
Eliminating the duplicates.

SELECT NAME, DOB FROM EMP WHERE COMPANY=CTS
UNION
SELECT NAME, DOB FROM EMPINFO WHERE COMPANY
=CTS;

Queries With Multiple Tables Updated

Uploaded by

Copyright:

Available Formats

Queries With Multiple Tables Updated

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Queries With Multiple Tables Updated

Uploaded by

Copyright:

Available Formats

QUERIES WITH

A database usually comprises of many tables.

We need to combine two tables.

Mention two table names in FROM clause.

The outer SELECT is called as the outer query.

Suppose we want a list of students whose total marks are

greater than the overall average.

SELECT NAME, TOTAL

The inner query is executed first and exactly once.

A join matches data from two or more tables based on the

Join is based on a primary key or foreign key make a good join.

1.SQL EQUI JOIN

Two types of equi joins:

DNO is a column in both the tables and need a way to be

Aliases are used to increase the readability of the sql.

SELECT EMPID, ENAME, DEPT, MANAGER

1.Left outer join

LEFT OUTER JOIN:

Does not find any matching records in right table B.

FULL OUTER JOIN:

Combines the result of both left and right outer join.

Comparing all columns in both tables that have the same

The resulting joined tables contains only one column for

SQL NON EQUI JOIN

Eliminating the duplicates.

You might also like