SQL JOINS


SQL Joins | SQL Tutorial | Minigranth

SQL JOINS : Introduction

  • SQL JOINS are the special clauses that are used to combine multiple tables present in a database on the basis of some common attributes present in those tables.
  • The SQL JOINS has the ability of combining two or more data tables/tuples into a single table/table only if the following conditions are satisfied.
    • There must be a common attribute in both(tables which are participating) tables.
    • Join condition must be satisfied.
  • The common attributes are compared using SQL Operators based upon the required conditions. The most oftenly used operator is the “=” (Equal To) symbol.
  • The concept of joins used in SQL are similar to that in DBMS except for the syntax. Also, SQL Joins uses “ON” clause. There are Five basic SQL Joins each of whose syntax and examples are explained in this chapter.
This image describes the various sql joins used in sql. These joins are exactly same which are used in dbms.
SQL Joins : Types

SQL JOINS : The INNER Join

  • The INNER JOIN which is also known as “SIMPLE JOIN or EQUIJOIN” is the most commonly used join in SQL. Queries equipped with INNER JOIN when executed returns all the rows which are present in the tables corresponding to the common attribute.

Syntax : SELECT Column_Name From Table_Name1 INNER JOIN Table_Name2 ON Table_Name1.Column_Name = Table_Name2.Column_Name;

For Example : Consider two tables student_details and student_result.

Table : student_details
this image describes the sample table used in joins in sql.

Table : student_result
this image describes the sample table used in joins in sql.

Query : SELECT * From student_details INNER JOIN student_result Where student_details.Roll_No = student_result.Roll_No;


Output :  This image describes the example of inner joins used in sql and dbms.

SQL JOINS : The NATURAL Join

  • The NATURAL JOIN is similar to INNER JOIN. The only difference is, redundant columns present in INNER JOINS are removed when NATURAL JOIN is used.
  • It works only if, one attribute or more than one attributes are common between the joining/participating relations.

Syntax : SELECT Column_Name From Table_Name1 NATURAL JOIN Table_Name2;


For Example : Consider two tables student_details and student_result.

Query : SELECT * From student_details NATURAL JOIN student_result;

Output 

This image describes the example of natural join used in dbms and sql.

SQL JOINS : LEFT OUTER JOIN

  • There exists a concept of position(left or right) of relations in case of both LEFT and RIGHT OUTER JOIN.
  • To implement LEFT OUTER JOIN, at least one entity needs to be common in the relations. All the attributes/tuples present in the left relation are recorded in the resulting relation along with those which are commonly present in the right relation.
  • If in case any tuple in left relation does not matches with the tuple in right relation, NULL value will be displayed against that tuple in the resulting relation.

Syntax : SELECT Column_Name From Table_Name1 LEFT JOIN Table_Name2 ON Table_Name1.Column_Name = Table_Name2.Column_Name;


For Example : Consider the tables student_details and student_result. Now, if we want to implement left outer join on these relations, the result will look like:

Query : SELECT Roll_No, Name, Address, Subject, Marks From Student_Details LEFT OUTER JOIN Student_Result ON Student_Details.Roll_No = Student_Result.Roll_No;


This image describes the left outer join used in sql and dbms.

SQL JOINS : The RIGHT OUTER Join

  • There exists a concept of position(left or right) of relations in case of both LEFT and RIGHT OUTER JOIN.
  • The RIGHT OUTER JOIN is completely similar to left outer join except the resulting relation will include all the tuples from relation present on right hand relation.
  • Also, NULL value will be displayed against the tuple which doesn’t matches up with the left side relation.

Syntax : SELECT Column_Name From Table_Name1 RIGHT JOIN Table_Name2 ON Table_Name1.Column_Name = Table_Name2.Column_Name;


For Example : Consider the tables Student_Details and Student_Result. Now, if we want to implement right outer join on these relations, the result will look like:

Query : SELECT Roll_No, Name, Address, Subject, Marks From Student_Details RIGHT OUTER JOIN Student_Result ON Student_Details.Roll_No = Student_Result.Roll_No;

This image describes the right outer join used in sql and dbms.

SQL JOINS : The FULL OUTER Join

  • In FULL OUTER JOIN, both the relations are merged together which results in a relation consisting of all the tuples.
  • If in case, tuples doesn’t matches, NULL value is passes against that.

Syntax : SELECT Column_Name From Table_Name1 FULL OUTER JOIN Table_Name2 ON Table_Name1.Column_Name = Table_Name2.Column_Name;


For example : Consider the tables Student_Details and Student_Result. Now, if we want to implement full outer join on these relations, the result will look like:

Query : SELECT Roll_No, Name, Address, Subject, Marks From Student_Details FULL OUTER JOIN Student_Result ON Student_Details.Roll_No = Student_Result.Roll_No;

This image describes the full outer join that is used in both dbms and sql.

NOTE : In the above description of various joins, some symbols are used in the screenshots of examples. They all have some certain meaning.

  1. ⋈: Natural Join
  2. : Left Outer Join
  3. : Right Outer Join
  4. : Full Outer Join

Previous Next