“SQL Joins” is used when there is a requirement to retrieve data from two or more tables in a database.
Let us take an example of two tables.
There are different types of Joins present in SQL, they are namely:
1.INNER JOIN:
It shows the result that is common in both the tables.
Syntax:
SELECT <col1,col2…> FROM <table1> INNER JOIN <table2> ON table1.column=table2.column.
This can be explained clearly using the above mentioned tables.
SELECT EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.EMPLOYEE_NAME,DEPARTMENT.DEPARTMENT_NAME FROM EMPLOYEE INNER JOIN DEPARTMENT ON EMPLOYEE.EMPLOYEE_ID=DEPARTMENT.EMPLOYEE_ID;
2.LEFT OUTER JOIN:
This will retrieve all the data from the left side of the table even if there is no match in the right side table.
Syntax:
SELECT <column1,column2…> FROM <table1> LEFT INNER JOIN <table2> ON table1.column=table2.column;
This can be explained with the following example using the above mentioned tables.
SELECT EMPLOYEE.EMPLOYEE_ID, EMPLOYEE.EMPLOYEE_NAME,DEPARTMENT.DEPARTMENT_ID FROM EMPLOYEE LEFT INNER JOIN DEPARTMENT ON EMPLOYEE.EMPLOYEE_ID = DEPARTMENT.EMPLOYEE_ID;
3.RIGHT OUTER JOIN:
This will retrieve all the data from the right side of the table even if there is no match in the left side of the table.
Syntax:
SELECT <column1,column2,…> FROM <table1> RIGHT OUTER JOIN <table2> ON table1.column = table2.column;
This can be explained with the following table using the above mentioned tables:
SELECT EMPLOYEE>EMPLOYEE_ID, EMPLOYEE.EMPLOYEE_NAME, DEPARTMENT.DEPARTMENT_NAME FROM EMPLOYEE RIGHT OUTER JOIN DEPARTMENT ON EMPLOYEE.EMPLOYEE_ID=DEPARTMENT.EMPLOYEE_ID;
Result:
5.Self Join:
This joins a table to itselfas if they were two tables, such that atleast one of the table in temporarily renamed.
Syntax:
SELECT <a.column_name1, b.column_name2…> FROM <table1 a, table2 b,…> WHERE a.common_field = b.common_field;
This can be explained with the following example:
Suppose we need to find out the list of all employees that has location same as the employee “Seema”.
SELECT a1.Employee_name FROM Employee1 a1, Employee1 a2 WHERE a1.Employee_location=a2.Employee_location and a1.Employee1=’Seema’;
Result:
6.Cartesian Join:
It retreives the cartesian product of set of records from two or more joined tables.
It is also called as Cross Join.In simple words we can explain this as follows:
a Cartesian join of (A,B) and (1,2,3) is (A,1) (A,2) (A,3) (B,1) (B,2) (B,3).
Syntax:
SELECT table1.coloumn1, table2.column2… FROM table1,table2,….
0r
SELECT * FROM table1 CROSS JOIN table2;
Suppose there are two tables namely Student and Section.
SELECT * FROM Student CROSS JOIN Section;
Result:
This is all about joins which is a very important aspect of writing queries as it combines two or more tables and hence reduce the complexities of writing long queries.
In my next blog I will be writing more about SQL queries.
beingtesting 