•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
SELECT column_name FROM table_name_1
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
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
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
3 comments:
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
Nice explanations of join ,thank for sharing...
retro jordans
nike kd 11
golden goose sneakers
ralph lauren uk
supreme clothing
louboutin
air max 90
moncler outlet
bape hoodie
yeezy boost 350