.png)
This is the most important topic in placement point of view, nearly in every Technical Interview questions are asked on Joins.
Now, an SQL join clause combines columns from one or more tables in a relational database.
We assume that the tables are mutually related using primary and foreign keys.
ANSI- standard SQL specifies five types of join:
1) Inner
2) Left Outer
3) Right Outer
4) Full Outer
5) Cross
Now, we have created two tables in "info" and "depart_info" for the sake of simplicity.
In "info" table department_id is foreign key
and
In table "depart_info" department_id is primary key.
Types of Joins:
CROSS JOIN
It is the simplest form of JOINs which matches each row from one database table to all rows of another table.
In other words it gives us cartesian product of rows from tables in join.
Query for cross join:
SELECT * FROM info CROSS JOIN depart_info;Also you can query using implicit cross join:
SELECT * FROM info, depart_info;After executing above script we get the following results:
INNER JOIN
It requires each row in the two joined tables to have matching column values, and is a commonly used join operation. So we can say that it "Returns records that have matching values in both tables".
Query for inner Join:
SELECT info.last_name, info.department_id, depart_info.depart_name FROM info INNER JOIN depart_info ON info.department_id=depart_info.department_id;After executing above script we get the following result:
OUTER JOIN
It returns all records matching from both tables. It just returns NULL values for records of joined table if no match is found. Depending on which table's rows are retained: left, right, or full. Now lets look them into example.
LEFT OUTER JOIN
The result of the left outer join for the two tables will always contains all the rows of the "left" table, even if the join-condition does not find any matching row in the "right" table. Where no matches have been found in the table on the right, NULL is returned.
Query for Left Outer Join:
SELECT * FROM info LEFT OUTER JOIN depart_info ON info.department_id= depart_info.department_id;After executing above script we get the following result:
RIGHT OUTER JOIN
It is well supposed opposite of Left Outer Join. It returns all the columns from the table on the right even if no matching rows have been found on the left table.
Where no matches have been found in the table on the left, NULL is returned.
Query for Right Outer Join:
SELECT * FROM info RIGHT OUTER JOIN depart_info ON info.department_id=depart_info.department_id;After executing above script we get the following result:
FULL OUTER JOIN
Conceptually a full outer combines the effect of applying both left and right outer joins. Where tables do not match, the result set will have NULL values for every column of the table where they do not match. For those that match, a single row will be produced in the result set.
"Returns all records when there is a match in either left or right table".
Query for Full Outer Join:
SELECT * FROM info LEFT OUTER JOIN depart_info ON info.department_id= depart_info.department_id
UNION
SELECT * FROM info RIGHT OUTER JOIN depart_info ON info.department_id=depart_info.department_id;After executing above script we get the following result:
Miscellaneous:
Till now we have discussed all basic joins that you should must know. Now you may think, why we use JOINs when you can do the same task running queries.
But using only one query you can get the work done instead of multiple successive queries.
JOINs have better performance over sub-queries.
JOINs can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.
We will discuss more stuff in other blogs.
Like, Share and comment please 😊❤ .
Follow Programmers Door for more stuff like this.