Database Joins
If you want to get data from multiple table data type using the single Select statement, you have to use joins. It gathers data from all the tables specified.
Types of Joins
Types of Joins:
Inner joins or equi-joins
Outer joins
Left outer join
Right outer join
Full outer join
Self joins
Examples:
Customers
create table customers(
customerid int primary key auto_increment not null, customername varchar(32),
email varchar(32), phone int,
cstatus varchar(32) check(cstatus in('enable','disable')));
Accounts
create table accounts( customerid int,
accountsid int primary key auto_increment not null,
accountstype varchar(2) check(accountstype in('sa','cu')), balance int, foreign key(customerid) references customers(customerid));
Address
create table address( customerid int, street varchar(32),
city varchar(32), country varchar(32),
foreign key(customerid) references customers(customerid));
Inner joins or equi-joins
Inner joins gives you making secrets from the joined tables.
Question 1: Display Customer name, email, account no., balance from customers and accounts tables.
Answer:
select customername, email, accountsid, balance from customers, accounts ;
This gives you product of both tables, which is invalid. So, when you are joining the tables you must remember to specify the join condition.
select customername, email, accountsid, balance from customers, accounts where customers.customerid = accounts.customerid;
This gives you exactly the matching records
Question 2: Display customer ID, customer name, status, city, country.
Answer:
select customers.customerid, customername, cstatus, city, country
from customers, address
where customers.customerid = address.customerid;
Question 3: Display customer name, account no, email, balance of the customers who have saving accounts.
Answer:
select customername, accountsid, email, balance
from customers, accounts
where customers.customerid = accounts.customerid and accountstype='sa';
Question 4: Display customer name, status, city, country of the customers who stay in Pune and are active.
Answer:
select customername, cstatus, city, country
from customers, address
where customers.customerid = address.customerid and city='pune' and cstatus='enable';
Question 5: Display account no, account type, Balance, city of the customers who have current account and balance between 10000 and 100000 and are staying in Pune or Mysore.
Answer:
select accountsid, accountstype, balance, city
from accounts, address
where accounts.customerid = address.customerid and accountstype='cu' and balance between 10000 and 1000000 and city in('pune','mysore');
Question 6: Display customer name, status, account no, balance, city and country of all the customers.
Answer:
select customername, cstatus, accountsid, balance, city, country
from customers, accounts, address
where customers.customerid = customers.customerid and customers.customerid = address.customerid;
Question 7: Display customer name, status, account no, balance, city, country of all the customers who are deactivated and have a balance of less than 1000 and are not staying in India.
Answer:
select customername, cstatus, accountsid, balance, city, country
from customers, accounts, address
where customers.customerid = customers.customerid and customers.customerid = address.customerid
and accountstype='cu' and cstatus='disable' and balance<1000 and country< >'india';
Left Outer Joins :
Left outer joins gives matched rows plus remaining rows on the left hand side table, with null values for the table on the right hand.
Question Display customer name, email, account no, balance from customers and accounts tables.
Answer:
select customername, email, accountsid, balance
from customers left join accounts
on customers.customerid = accounts.customerid;
New Syntax:
(For left Outer Join)
Select account customer ID, customer name, email, account no, balance from customers customer left outer join account,account no account customer ID = customer ID
New Syntax:
(For Inner Join)
Select customers.customerid,customername, email, accountsid,balance from customers INNER JOIN accounts on customers.customerid = accounts.customerid;
Right Outer Joins:
Its same as left outer join, but the only difference is that it will give the matching records + remaining rows of right hand side table.
Right outer joins gives you matching records plus records remaining in the right side table.
Example:
select customers.customerid, customername, email, accountsid, balance from customers right outer join accounts on customers.customerid = accounts.customerid;
Full Outer Joins
In mysql it is the combination of left join union right join, mysql avoids full outer join.
Full outer joins gives you matching record plus records remaining on the left side table plus the right side table.
Note: In mysql, there is no full outer join. It is either left outer join union or right outer join.
Example:
select *
from customers left join accounts
on customers.customerid = accounts.customerid union
select * from customers right join accounts
on customers.customerid = accounts.customerid;
Self Joins
Joining in the table itself is called self-join.
Example:
select a.employeeid,a.email, m.managerid
from employee a inner join employee m
where a.employeeid = m.managerid;
