SQL Joins
In this article lets understand in detail about Joins.
* Joins help us in retrieving the data from two or more database tables.
*The tables are mutually related using primary and foreign keys.
Type of Joins:
1)Equal Join/inner Join/Simple Join
2)Right Join
3)Left Join
4)Full Join
5)Self Join
Note: In order to establish relation between these 2 tables there must be at least one common column.
Consider
Table 1 with data 10,11,12,14
Table 2 with data 11,12,13,15
Inner Join
This will return the common data from tables.
*In both the Tables NUMID column is common hence we can use the common Column Name while using Inner Join Condition.
Command:
SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.NUMID=TABLE2.NUMID;
*The output will be (11,12) as per the data given in above tables.
*Also, if we do not want the common data to be the output then we can use not equal to ‘<>’ symbol.
Command:
SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.NUMID<>TABLE2.NUMID;
LEFT Join:
In this, data will be retrieved from the left table.
Command:
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.NUMID=TABLE2.NUMID;
*The output will be (10,11,12,14)
RIGHT Join:
Data will be retrieved from the right table.
Command:
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON TABLE1.NUMID=TABLE2.NUMID
*The output will be (13,15,11,12)
FULL Join:
This is a combination of right and left join. However this is not supported by MySQL.
Command:
SELECT * FROM TABLE1 FULL JOIN TABLE2 T2 ON T1.NUMID=T2.NUMID;
*The output will be (10,11,12,14,13,15)
Note:
If there is a common column in 2 tables while executing the command there will be an error and the output will not be displayed.
Example: In EMPLOYEE AND DEPARTMENT tables there is a common column called DEPARTMENT_ID and if we execute the below command we will get an error.
Command:
SELECT EMPLOYEE_ID,FIRST_NAME,JOB_ID,DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID
FROM EMPLOYEES LEFT JOIN DEPARTMENTS
ON (DEPARTMENT_ID=DEPARTMENT_ID);
*However in order to have the right output the command would be
SELECT EMPLOYEE_ID,FIRST_NAME,JOB_ID,DEPARTMENT.DEPARTMENT _ID,DEPARTMENT_NAME,LOCATION_ID
FROM EMPLOYEES LEFT JOIN DEPARTMENTS
ON (EMPLOYEES .DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID); // we have mentioned the table name before the common column name in both the tables.
Similarly,
EMPLOYEE_ID,FIRST_NAME,JOB_ID,DEPARTMENT.DEPARTMENT_ID,DEPARTMENT_NAME,LOCATION_ID
FROM EMPLOYEES RIGHT JOIN DEPARTMENTS
ON (EMPLOYEES .DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID); // we have mentioned the table name before the common column name in both the tables.
Note :
If there are no matching records available then it would display as NULL.
SELF Join:
We will have single table and if we want to join the single table with the same table.
*Lets say we have employee table and empid, first name, manager name as column names
empid firstname manager
101 a 103
102 b 106
103 c 100
*Every manager has a empid hence if the value in the empid is matching with the value in the manager column then they are the managers.
*Let us understand that 103 is a manager as the manager is one of the employee of the organization he has an empid.
*That is 103 is listed under empid and manager column hence he comes under manager the same is not with 101,102.
*Now the query is print the employee details who is manager of other employees
*In this case we need to split the table into 2 parts that is create ALIAS(ANOTHER NAME FOR THE TABLE)of the table lets say E AND M.
Command:
SELECT E.EMPLOYEE_ID,M.MANAGER_ID,E.FIRST_NAME,M.FIRST_NAME
FROM EMPLOYEES E,EMPLOYEES M WHERE E.EMPLOYEE_ID=M.MANAGER_ID;
That is all about Joins. In the next part we will learn about Sub Queries in SQL.
