Joins - Selecting from multiple tables

Practically, data from more than one table is required to serve end-user applications.
For example, a web page might need to show employee name, employeed id, deptt id, deptt city and salary in tabular format.
In such case, tables having department details and employee details need to be joined to get required output.
Let's prepare the data and run a join query.

drop table deptt;
create table deptt(
   depid int not null auto_increment,
   depname varchar(30) not null,
   depcity varchar(20) not null,
   depstreet varchar(30) not null,
   depopendate date,
   primary key ( depid )
);

insert into deptt (depname,depcity, depstreet,depopendate)
values('Accounts','Bengaluru','Daker Street',now());
insert into deptt (depname,depcity, depstreet,depopendate)
values('HR','Delhi','Baker Street',now());
insert into deptt (depname,depcity, depstreet,depopendate)
values('Finance','Mumbai','Laker Street',now());

drop table emp;
create table emp(
   empid int not null auto_increment,
   empfname varchar(100) not null,
   emplname varchar(100) not null,
   depid int not null,
   doj date,
   empsal int not null,
   mgrempid int,
   primary key ( empid ),
   foreign key (depid) references deptt(depid) on delete restrict
);

insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Sam','Fox',1,'2016-06-22',2200,null);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('John','Doe',1,'2017-05-17',1600,1);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Beck','Dot',2,'2014-09-10',2500,1);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Jack','Den',2,'2017-10-28',2400,3);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Emil','Bon',3,'2018-01-20',2100,1);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Siera','Leck',3,'2014-02-17',2000,4);
insert into emp (empfname, emplname, depid, doj, empsal,mgrempid)
values('Koe','Hoker',3,'2016-03-16',2900,4);

So, now we have 3 departments and 7 employees.
We want to display department and employee details in a single output.
Here, deptt and emp tables are related to each other via depid column and generally, and typically such a column is used to join 2 tables.

select e.empid, e.empfname, e.emplname, e.depid, e.doj, e.empsal, d.depname, d.depcity
from emp e, deptt d
where e.depid = d.depid
order by empid,empfname,emplname
;

Previous Index Next

SQL Tutorial

Joins - Selecting from multiple tables

XP

Loading comments...