Author: Shameer
•10:28 AM
JOIN
The SQL JOIN clause is used whenever we have to select data from 2 or more tables. There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don't specify INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short ‘INNER JOIN’ = ‘JOIN’. Different databases have different syntax for their JOIN clauses).

INNER JOIN
The INNER JOIN keyword return rows when there is at least one match in both tables.

SYNTAX

SELECT column_name FROM table_name_1
INNER JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example
Table 1: Department


Table 2: Student


OUTPUT

SELECT Department.Dept_name, Student.Stud_name
From Department
JOIN Student
ON Department.Dept_Id = Student.Dept_Id
The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN (LEFT JOIN) and RIGHT OUTER JOIN (RIGHT JOIN).

LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (table_name_1), even if there are no matches in the right table (table_name_2).
SYNTAX

SELECT column_name FROM table_name_1
LEFT JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example
Table 1: Department



Table 2: Student



OUTPUT

SELECT Department.Dept_name, Student.Stud_name
From Department
LEFT JOIN Student
ON Department.Dept_Id = Student.Dept_Id


RIGHT JOIN
The RIGHT JOIN keyword returns all rows from the right table (table_name_2), even if there are no matches in the left table (table_name_1).

SYNTAX

SELECT column_name FROM table_name_1
RIGHT JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example

Table 1: Department


Table 2: Student


OUTPUT

SELECT Department.Dept_name, Student.Stud_name
From Department
RIGHT JOIN Student
ON Department.Dept_Id = Student.Dept_Id


FULL JOIN
The FULL JOIN returns all the rows from the left table, and all the rows from the right table.

SYNTAX

SELECT column_name FROM table_name_1
FULL JOIN table_name_2
ON table_name_1. column_name = table_name_2.column_name

Example

Table 1: Department


Table 2: Student


OUTPUT

SELECT Department.Dept_name, Student.Stud_name
FROM Department
FULL JOIN Student
ON Department.Dept_Id = Student.Dept_Id


This entry was posted on 10:28 AM and is filed under . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 comments:

On December 26, 2016 at 5:25 AM , Anonymous said...

Thank you for the fresh content and information you provide. This really helps learner like me. Keep sharing knowledge. Happy to read.
more about the authorSchool website design uk

 
On October 6, 2017 at 10:56 PM , शिवलिंग बोडके said...

Nice explanations of join ,thank for sharing...

 
On December 18, 2019 at 3:30 AM , yanmaneee said...

retro jordans
nike kd 11
golden goose sneakers
ralph lauren uk
supreme clothing
louboutin
air max 90
moncler outlet
bape hoodie
yeezy boost 350