0 votes
in DBMS by

4 major types of Joins are used while working on multiple tables in SQL databases:

INNER JOIN: It is also known as SIMPLE JOIN which returns all rows from BOTH tables when it has at least one matching column.

Syntax:

SELECT column_name(s)

 FROM table_name1 

 INNER JOIN table_name2

 ON column_name1=column_name2;

For Example,

In this example, we have a table Employee with the following data:

Employee table

The second table's name is Joining.

joining

Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date

  FROM Employee

  INNER JOIN Joining

  ON Employee.Emp_id = Joining.Emp_id

  ORDER BY Employee.Emp_id;

There will be 4 records selected. Results are:

result of innerjoin

Employee and Orders tables have a matching customer_id value.

LEFT JOIN (LEFT OUTER JOIN): This join returns all rows from the LEFT table and its matched rows from a RIGHT table.

Syntax:

SELECT column_name(s)

 FROM table_name1

 LEFT JOIN table_name2

 ON column_name1=column_name2;

For Example,

In this example, we have a table Employee with the following data:

Employee table

The second table's name is Joining.

joining 1

Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date

FROM Employee

LEFT OUTER JOIN Joining

ON Employee.Emp_id = Joining.Emp_id

ORDER BY Employee.Emp_id;

There will be 4 records selected. You will see the following results:

result of LEFT OUTER JOIN

RIGHT JOIN (RIGHT OUTER JOIN): This joins returns all rows from the RIGHT table and its matched rows from the LEFT table.

Syntax:

SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON column_name1=column_name2;

For Example,

In this example, we have a table Employee with the following data:

Employee table

The second table's name is Joining.

joining table

Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date FROM Employee

RIGHT JOIN Joining

ON Employee.Emp_id = Joining.Emp_id

ORDER BY Employee.Emp_id;

Output:

Emp_idJoining_Date
E00122016/04/18
E00132016/04/19
E00142016/05/01

FULL JOIN (FULL OUTER JOIN): This joins returns all results when there is a match either in the RIGHT table or in the LEFT table.

Syntax:

SELECT column_name(s)

 FROM table_name1

 FULL OUTER JOIN table_name2

 ON column_name1=column_name2;

For Example,

In this example, we have a table Employee with the following data:

Employee table

The second table's name is Joining.

joining 1

Enter the following SQL statement:

SELECT Employee.Emp_id, Joining.Joining_Date

FROM Employee

FULL OUTER JOIN Joining

ON Employee.Emp_id = Joining.Emp_id

ORDER BY Employee.Emp_id;

There will be 8 records selected. These are the results that you should see.

result of FULL OUTER JOIN

Related questions

0 votes
0 votes
0 votes
0 votes
asked Apr 22, 2020 in DBMS by Hodge
0 votes
0 votes
asked Dec 13, 2020 in Sql by SakshiSharma
...