SQL JOINS (Inner, Left, Right and Full Joins)

SQL Join?statement is used to combine data or rows from two or more tables based on a common field between them.?Different types of Joins are as follows:?

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• NATURAL JOIN?

The following is a brief overview of the different types of JOINs:

An?INNER JOIN?returns rows when the join condition is satisfied in both tables. In other words, it returns only those records that match the join condition in both tables. (Learn more about?INNER JOINs here.) This is the most common type of SQL JOIN. It’s also the default when you don’t specify the type of JOIN.

An OUTER JOIN returns all the rows from one table and some or all of the rows from another table. (Learn more about?OUTER JOINs here.) There are three types of outer joins:

A?LEFT JOIN?returns all rows from the?left?table, even if no matching rows have been found in the right table. If there are no matches in the right table, the query will return NULL values for those columns. One of our previous articles provided a more in-depth look at?LEFT JOINs?that I think you’ll find very useful.

A?RIGHT JOIN?returns all rows from the?right?table. If there are no matches in the left table, NULL values are returned for those columns.

A?FULL JOIN?is essentially a combination of?LEFT JOIN?and?RIGHT JOIN. It returns all rows from both tables. Where no match is found in either the right or left table, it returns NULLs for those columns. In other words, it is the union of columns of the two tables.

Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.?

/*CUSTOMERS TABLE */

CREATE TABLE CUSTOMERS(
? ID ? INT ? ? ? ? ? ? ?NOT NULL,
? NAME VARCHAR (20) ? ? NOT NULL,
? AGE ?INT ? ? ? ? ? ? ?NOT NULL,
? ADDRESS ?CHAR (25) ,
? SALARY ? DECIMAL (18, 2), ?/* The (18,2) simply means that we can have 18 digits with 2 of them after decimal point*/
? PRIMARY KEY (ID)
);

/*INSERTING DATA INTO CUSTOMERS TABLE*/
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (1, 'Mark', 32, 'Texas', 50000.00 );


INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (2, 'John', 25, 'NY', 65000.00 );


INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (3, 'Emily', 23, 'Ohio', 20000.00 );


INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (4, 'Bill', 25, 'Chicago', 75000.00 );


INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (5, 'Tom', 27, 'Washington', 35000.00 );


INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (6, 'Jane', 22, 'Texas', 45000.00 );


/* ORDERS table */
CREATE TABLE ORDERS(
? ORDER_ID ? INT ? ? ? ?NOT NULL,
? DATE VARCHAR (20) ? ? NOT NULL,
? CUSTOMER_ID ?INT ? ? ?NOT NULL,
? AMOUNT ? INT,
? PRIMARY KEY (ORDER_ID),
? FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID) /* We must specify the table to which this foriegn key refers*/
);

/*INSERTING DATA INTO ORDERSThe  TABLE*/
INSERT INTO ORDERS (ORDER_ID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (100, '2019-09-08', 2, 5000 );


INSERT INTO ORDERS (ORDER_ID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (101, '2019-08-20', 5, 3000 );


INSERT INTO ORDERS (ORDER_ID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (102, '2019-05-12', 1, 1000 );


INSERT INTO ORDERS (ORDER_ID, DATE, CUSTOMER_ID, AMOUNT)
VALUES (103, '2019-02-02', 2, 2000 );        

The tabular representation of data

Notice that the?CUSTOMER_ID?in the ORDER table references?ID?in the CUSTOMER table.

Now, what if we need to query something that is the combination of information in both tables?

For example, we want to:

• Find information on customers who ordered an item.
• Find the number of customers who ordered a certain item.
• Find the address of a customer in order to dispatch the order.

The joins in SQL can help you do that using the?JOIN?clause.

1. INNER JOIN

The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same.?

Syntax:?

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.        

Note: We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.?

Let’s say we want to retrieve the information of only those customers that have placed an order. This can be done by joining the two tables:

SELECT ?CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.AMOUNT, ORDERS.DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;        

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the “Orders” table that do not have matches in “Customers”, these orders will not be shown.

That is why we don’t see Emily, Bill or Jane in the result-set; they have not placed any orders.

Which of the following queries will return the NAME and AGE of a customer along with the DATE they placed an order?

A)

SELECT?CUSTOMERS.NAME,?CUSTOMERS.AGE,?ORDERS.DATE
FROM?CUSTOMERS
INNER?JOIN?ORDERS
ON?CUSTOMERS.ID?=?ORDERS.CUSTOMER_ID;        

B)

SELECT?NAME,?AGE,?DATE
FROM?CUSTOMERS
INNER?JOIN?ORDERS
ON?CUSTOMERS.ID?=?ORDERS.CUSTOMER_ID;        

C)

SELECT?CUSTOMERS.NAME,?CUSTOMERS.AGE,?ORDERS.DATE
FROM?CUSTOMERS
INNER?JOIN?ORDERS
ON?CUSTOMERS.ID?=?ORDERS.CUSTOMER_ID        

D)

SELECT?CUSTOMERS.NAME,?CUSTOMERS.AGE,?ORDERS.DATE
FROM?CUSTOMERS
INNER?JOIN?ORDERS
ON?CUSTOMERS.NAME?=?ORDERS.CUSTOMER_ID;        

2. LEFT JOIN

This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result-set will contain?null. LEFT JOIN is also known as LEFT OUTER JOIN.

Syntax:?

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.        

Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same.

Example: Let’s say we want to return all orders and any customers that have placed an order:

The SQL query to retrieve all orders and some of the customers(those who have placed an order):

SELECT ?CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.AMOUNT, ORDERS.DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;        

Note: As you can see, the RIGHT JOIN keyword returns all records from the right table (ORDERS), even if there are no matches in the left table (CUSTOMERS).

D. FULL JOIN

FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain?NULL?values.

Syntax:??

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.         

The keyword OUTER

OK. Remember when we told you that?JOIN?is short for?INNER?JOIN

The three joins we mentioned just now:?LEFT?JOIN,?RIGHT?JOIN, and?FULL?JOIN?are also shortcuts. They are all actually?OUTER?JOINS:?LEFT?OUTER?JOIN,?RIGHT?OUTER?JOIN, and?FULL?OUTER?JOIN. You can add the keyword?OUTER?and the results of your queries will stay the same.

Exercise1: Problem statement

Write a SQL query to fetch the count of employees working on project ‘P1’.

-- SOLUTION 1

SELECT COUNT(EMP_ID)
FROM SALARY 
WHERE PROJECT = 'P1';

-- SOLUTION 2

SELECT COUNT(s.Emp_id )FROM EMPLOYEE AS e
JOIN SALARY AS s 
on e.Emp_Id = s.Emp_Id
where project = 'p1';         

Exercise2: Problem statement

Write a SQL query to fetch employee names having a salary greater than or equal to $40,000 and less than or equal $60,000.

SELECT E.FULL_NAME, S.SALARY
FROM EMPLOYEE AS E
INNER JOIN SALARY AS S
ON E.EMP_ID = S.EMP_ID
WHERE S.SALARY >= 40000 AND S.SALARY <= 60000;

-- The solution will also work without using the aliases as seen below:

SELECT EMPLOYEE.FULL_NAME, SALARY.SALARY
FROM EMPLOYEE
INNER JOIN SALARY
ON EMPLOYEE.EMP_ID = SALARY.EMP_ID
WHERE SALARY.SALARY >= 40000 AND SALARY.SALARY <= 60000;        

Exercise 3

Problem statement

Write a SQL query to fetch a project-wise count of employees sorted by the project’s count in descending order.

SELECT PROJECT, COUNT(Emp_Id) AS Emp_Count
FROM SALARY 
GROUP BY PROJECT 
ORDER BY Emp_Count DESC;

Output
project	count(Emp_Id)
P1	2
P2	2
P3	2
P4	1
P5	2