Table Joins and Indexes in SQL
Fetching data from multiple tables
Fetching data in faster way
emp
emp
Join
• A join is a query that combines rows from two
of more tables.
• In JOIN query more than one table are listed
in FROM clause.
• MySQL provides various type of Joining :
1) CROSS JOIN or CARTESIAN PRODUCT
2) EQUI-JOIN
3) NATURAL JOIN
Cross Join (Cartesian product)
• It return all possible concatenation of all rows from
both table i.e. one row of First table is joined with
all the rows of second table.
• Cartesian product join each row of one table with
each row of another table.
• So if –
• First table have 6 rows and second table have 4 rows
then total number of rows in output will be 6 x 4 = 24.
• i.e. Total Number of Rows after Cartesian
product(Cardinality) = Cardinality of First Table X
Cardinality of Second Table
Cross Join (Cartesian product)
• For Example
▫ Select * from Color,Shades;
• Output will contain 9 rows i.e. no. of rows in first
table x no. of rows in second table
Cross Join (Cartesian product)
Cross Join (Cartesian product)
Equi-join
• The join in which columns are compared for
equality is called Equi-Join.
• In equi-join we put(*) in the select list therefore the
common column will appear twice in the output.
Table : empnew
Table : dept
Select * from empnew, dept where
[Link]=[Link]
Equi-join
Now we want to
fetch details of
employee along
with its
corresponding
matching
department. Like
for ‘alam’ deptno
is 10 so from dept
table it should
show deptno 10
details and so on
Equi-join
Common
Column
appears
twice in
output
From the above query, we can observe that while doing equi-join we have to give
equality condition on common column of both table so that it picks related
records
Equi-join
We can also give Table Alias i.e. ni ck name for table name an d further we can use
this name any where in query in place of table name. This is helpful when table
name is of big length and we can shorten the query
Natural Join
• The JOIN in which only one of the identical columns
exists in called Natural Join.
• It is similar to Equi-join except that duplicate
columns are eliminated in Natural join that would
otherwise appear in Equi-Join.
• In natural join we specify the names of column to
fetch in place of (*) which is responsible of
appearing common column twice in output.
See hereVI,NOwD KeUMaArReVERnMoA,tPGgT(iCvS),iKnV OgEF KANPUR &
*, like in equi-join but we
are giving list of columns to
Natural Join
fetch
Natural Join
The reason of this error
is – the deptno exists in To resolve this error,
both the table, so in this
just qualify the
case if we are selecting or
using only deptno then it
common column by
becomes ambiguous table name as
from which table this [Link]
deptno will be selected name
Natural Join
Additional condition in joins
Joining Tables using JOIN clause of SQL
SELECT
• MySQL support various options with JOIN
▫ CROSS
▫ NATURAL
▫ ON
▫ USING
Cartesian product using JOIN
• Select * from shades JOIN color;
• Or
• Select * from shades CROSS JOIN color;
Equi – Join using JOIN
• Select * from emp JOIN dept ON [Link] =
[Link];
• Select * from emp JOIN dept ON [Link] =
[Link] where salary>50000;
Natural – Join using JOIN
• Select * from emp NATURAL JOIN dept;
In NATURAL JOIN condition the join condition is not required it automatically
joins based on the common column value
STATIONERY_NEW
S_ID Stationary Company Price Stock Date
Name
DP01 Dot Pen ABC 12 2011-03-31
ER05 Eraser XYZ 8 2010-01-01
PL02 Pencil XYZ 10 2010-01-01
CONSUMER
C_ID Consumer Address S_ID
name
1 Good Learner Delhi DP01
15 Write and Delhi PL02
Draw
16 Topper Mumbai DP02
MYSQL- STATIONERY AND CONSUMER
1. Display the details of all stationary items in descending order of
stock date.
2. Display the details of stationary items whose company is XYZ and
price is below 10.
3. Display C_ID, consumer name,S_ID, Stationary name and price
from the tables.
4. Increase the price of all stationary items by 200
5. Display the number of consumers based on place
EMPLOYEE
EmployeeID Name Sales JOBID
E1 Sumit Singh 110000 102
E2 Vijay Singh 130000 101
E3 Ajay Rajpal 140000 103
E4 Mohit Ramnani 125000 102
E5 Shailja Singh 145000 103
JOB
JOBID Job Title Salary
101 President 200000
102 Vice President 125000
103 Admin Assisstant 80000
104 Accounts Manager 70000
MYSQL TABLE- EMPLOYEE AND JOB
[Link] the employee ids, name of employees, job ids with
corresponding job titles.
2. Display names of employees, sales and corresponding job titles
who have achieved sales more than 130000
3. Display names and job titles of those employees who have Singh
anywhere in their names.
4. Display the average sales job wise.
5. Change the jobid of employee E4 to 104.
ITEM
Icode Iname Price Colour Vcode
S001 Refrigerator 20000 Blue P01
S002 Mobile Phone 45000 Black P02
S003 LCD 60000 Silver P03
S004 Washing Machine 12500 Smoke P01
S005 Air Conditoner 16000 White P03
S006 Refrigerator 15000 White P02
VENDOR
Vcode Vname Vcity
P01 Satish Mumbai
P02 Manoj Delhi
P03 Subodh Chennai
P04 Jacob Banglore
MYSQL TABLES-ITEM AND VENDOR
1. Display Item code, item name, Vendor name and
vendor city of all the vendors, who manufacture
“Refrigerator”.
2. Display Item code, Item name and price of all the
products whose price is more than 20000 and
vendor from Delhi.
3. Display the count of items for each vendor code
4. Display the average price of items vendor code wise.
5. Insert into vendor values(“P05”,”Martin”,”Banglore”)