In this tutorial, l’m going to touch on one of the most common statements you’ll see on daily basis when writing sql in real time programming environment. Joins are used for combining multiple tables based some similarities among those tables. During normalization, some tables may be broken down into two or more tables in order to have a normalized database and avoid redundancy. During searches, you might have to span across all those tables to retrieve related columns for your required output. Joins and Sub queries work very similar but joins have more advantage over sub queries. Whatever you can do in a sub query, you can do it with a join but you can do everything in joins with a sub query. We’ll be looking at sub queries very soon but the main advantage of joins over sub queries is that joins give you access to all columns from all the tables being considered but sub queries give access to columns of the outer query only.
There are 5 major types of joins, These are inner join, left outer join, right outer join, full outer join and cross join. we also have the logical joins which are simply extension of some of the major joins.
1.Inner join: gets all the matching records from both tables based on joining column.
Let’s create two tables and see how to use inner join on it.
CREATE TABLE employee(empid int, contactid int ,hiredate date,managerid)
CREATE TABLE contact(contactid int, firstname varchar(20), lastname varchar(20))
The employee table contains all employees of the company and the contact table contains contact information of all people who are employees, vendors, contractors etc. So how can we use these tables to get all employees with their first name and last name? This is a good place to use an inner join to join the two tables on one common column, which is the contactid because it’s available in both tables. That means, to retrieve information about a particular employee, you just look at the employee id, get his/her contact id then go to the contact table to check the matching information for that contact id. This can be done with sub queries as well but what about if we want to retrieve columns from both tables like employeeid from employee table and firstname and lastname from the contact table, joins are perfect for this. So let’s look at the code to get information for employees and their contact information.
SELECT e.empid, c.firstname, c.lastname
FROM employee as e
INNER JOIN contact as c
ON e.contactid = c.contactid
This will give us all employee ids and their corresponding first and last names.
2.left outer join:gets all non-matching records from left table and set of matching records from both tables
Based on the table we created above, In the query for INNER JOIN, we retrieve information for all employees. What about if you want to find information on people who are not employees of the company? Remember the contact table contains both employees and non employees information. We can use either left or right outer join to accomplish this task.
SELECT c.firstname, c.lastname
FROM contact as c
LEFT OUTER JOIN employee as e
ON c.contactid = e.contactid
3.right outer join: get all non-matching records from right table and one copy of matching records from both tables. The right outer join can be written using the left outer join and the left outer join can be written using the right outer join. To accomplish the same task as we did in the left outer join, we just change the location of the tables and switch the left for right, as shown below.
SELECT c.firstname, c.lastname
FROM employee as e
RIGHT OUTER JOIN contact as c
ON c.contactid = e.contactid
4.full outer join:gets all non-matching records from left table and all non-matching records from right table and one copy of matching records from both tables
5. Cross join: cartesian product of two tables
There are some extensions of the joins, these are called Logical joins. Examples are
self-join:joining a table to itself
Based on the table we created above, we see a column for the managerid for an employee. That means manager id is for an employee who manages that current employee record under consideration. How do we find information about only managers? The self join can help us accomplish this task. Lets see the code.
SELECT m.empid, c.firstname, c.lastname
FROM employee as e
INNER JOIN employee as m
ON e.empid = m.managerid
INNER JOIN contact as c
ON c.contactid = m.contactid
restricted left outer join: gets all non-matching records from the left table
restricted right outer join: gets all non-matching records from the right table
restricted full outer join:gets all non-matching records from left table and all non-matching records from right table.
Hope this helps, if you find something confusing, please drop a message.
Our next tutorial will be on SET OPERATORS. Keep in touch, Peace.
