Why Join is important in SQL #DOTC_Mdb

Why Join is Important in SQL.

 

Hello All,

Today I will discuss why SQL Joins is important in database , before going the importance of Join in SQL  Please go through my previous blogs i.e. SQL and Classification of SQL so it’s easy for you to understand joins.

 https://dheeraj60.blogspot.com/2020/05/importance-of-sql-and-classification-of.html

https://dheeraj60.blogspot.com/2020/05/sql-commands-uses-and-benefits-which.html

Why Join is important:

The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each. To stitch the database back together to make it easy to read and use database joins are used. They match rows between tables. In most cases we're matching a column value from one table with another. So In Simple word we can say Joins is basically used to get the data from one or more tables to get the required data and Joins can be of different types which can be used for trend analysis in any organization.

Let’s Understand different kind of Oracle joins that allow you to query data from two or more tables and get the required output.

Oracle SQL joins used to combine columns from two or more tables by comparing the values of related columns . The related columns can be the primary columns of the first table and foreign key column of second table.

Important : Main advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query

In my previous blog I already explained primary key and foreign key .

Note : Without using primary key and foreign key we can also get result but logically we should use primary and foreign key to relate the columns and get the output in proper way.

In Oracle or other RDBMS Database supports below Joins.

1.      Equi Join

2.      Self Join

3.      Left Join

4.      Right Join

5.      Cross Join

6.      FULL Join

We will define and use above joins when we created at least two tables and some data in table.

Creating two tables by using SQL to understand joins by using SQL DDL create command.

 

CREATE  TABLE STUDENT

(ID INT PRIMARY KEY , 

 Name Varchar2(20)  

);

 

If you install My SQL in your System Varchar2 will not support so you can simply use Varchar instead of Varchar2 . Varchar2 supports in Oracle , Sybase , DB2 etc database . Since My SQL is free ware so for learning you can use varchar.

 

In this table first column id -- ---  int is data type and primary key is constraint

2nd column Name --- Data type is Varchar2(20)

 

CREATE  TABLE STUDENT_DETAILS

(ID INT  , 

 ADDRESS Varchar2(20)  

);

 

In this table I simply put data types for both the columns. int is for number and varchar2
is for character.

 

 

---- Now we insert the data in table STUDENT by using one simple command of SQL DML i.e. INSERT

 

---- Inserting data in STUDENT TABLE

 

INSERT INTO STUDENT (ID,NAME) VALUES (1, 'Ashish');

INSERT INTO STUDENT (ID,NAME) VALUES (2, 'Prashant');

INSERT INTO STUDENT (ID,NAME) VALUES (3, 'Deepak');

INSERT INTO STUDENT (ID,NAME) VALUES (4, 'Krishna');

 

Note : For int data type no need to put any quotes but for character we need to put single quotes.

 

-- Inserting data in STUDENT_DETAILS TABLE

 

INSERT INTO STUDENT_DETAILS (ID,Address) VALUES (1, 'MDB');

INSERT INTO STUDENT_DETAILS (ID,ADDRESS) VALUES (2, 'DBG');

INSERT INTO STUDENT_DETAILS (ID,ADDRESS) VALUES (5, 'PAT');

INSERT INTO STUDENT_DETAILS (ID,ADDRESS) VALUES (6, 'MZB');

 

We have inserted 4 values in both values with some similar ID and  other ID so that we can see the output which matched or not matched by using several Joins;

 

Let see the table values of both table by using select statement

 

mysql> select * from student;

+----+----------+

| ID | Name     |

+----+----------+

|  1 | Ashish   |

|  2 | Prashant |

|  3 | Deepak   |

|  4 | Krishna  |

+----+----------+

 

 

mysql> select * from student_details;

+------+---------+

| ID   | ADDRESS |

+------+---------+

|    1 | MDB     |

|    2 | DBG     |

|    5 | PAT     |

|    6 | MZB     |

+------+---------+

 

 

 

1.      Equi Join:  Equi Join is basically used to find the matched records from the tables .

 

Syntax and output :

 

mysql> select a.id,a.name , b.id, b.address from student a , student_details b where a.id=b.id;

 

+----+----------+------+---------+

| id | name     | id   | address |

+----+----------+------+---------+

|  1 | Ashish   |    1 | MDB     |

|  2 | Prashant |    2 | DBG     |

+----+----------+------+---------+

 

Here we can see we got only 2 records as it’s matched records in both table id is 1 and 2. Other unmatched record will not display.

 

Note : Here we use a and b as alias for tables to our convenient as it’s easy to use in condition . We can also directly use table name instead of alias but it’s better to use alias if we join n number of tables then it’s easy for us to put condition via alias.

 

2.      Self Join : Join the table itself is known as Self join .  table join to itself to query hierarchical data using an inner join, left join, or right join is also known as self Join.

 

 

Syntax and Output :

 

mysql> SELECT

             a.id ,

             a.name,

            b.id,

            b.address

            FROM

           student a

          INNER JOIN student_details b  ON a.id = b.id;

+----+----------+------+---------+

| id | name     | id   | address |

+----+----------+------+---------+

|  1 | Ashish   |    1 | MDB     |

|  2 | Prashant |    2 | DBG     |

+----+----------+------+---------+

 

Here we also see the 2 output , but in self join it depends we can join table itself , inner join , left join and right join . As per data we use inner join tha’s why the output is 2 and similar to equi join.

 

 

Note : Here we use a and b as alias for tables to our convenient as it’s easy to use in condition . We can also directly use table name instead of alias but it’s better to use alias if we join n number of tables then it’s easy for us to put condition via alias.

 

3.      Left Join:  The left Join is basically to get all the records from Left table and  only matched records from right table .

 

 

Syntax and Output:

 

mysql> SELECT

         a.id id,

         a.name,

         b.id,

        b.address

     FROM

        student a

    LEFT JOIN student_details b ON a.id = b.id;

+----+----------+------+---------+

| id | name     | id   | address |

+----+----------+------+---------+

|  1 | Ashish   |    1 | MDB     |

|  2 | Prashant |    2 | DBG     |

|  3 | Deepak   | NULL | NULL    |

|  4 | Krishna  | NULL | NULL    |

+----+----------+------+---------+

4 rows in set (0.07 sec)

 

Here we see the output from 2nd table i.e student_details only matched record is showing and other values are null but in left table Student It’s showing all the values . This is Left Join

 

 

Note : Here we use a and b as alias for tables to our convenient as it’s easy to use in condition . We can also directly use table name instead of alias but it’s better to use alias if we join n number of tables then it’s easy for us to put condition via alias.

 

 

4 . Right Join : The Right Join is basically to get all the records from Right table and  only matched records from left  table just vice versa we can say after seeing the Left Join .

 

Syntax and Output:

 

mysql> SELECT

             a.id id,

             a.name,

             b.id,

             b.address

            FROM

           student a

          RIGHT JOIN student_details b ON a.id = b.id;

+------+----------+------+---------+

| id   | name     | id   | address |

+------+----------+------+---------+

|    1 | Ashish   |    1 | MDB     |

|    2 | Prashant |    2 | DBG     |

| NULL | NULL     |    5 | PAT     |

| NULL | NULL     |    6 | MZB     |

+------+----------+------+---------+

4 rows in set (0.00 sec)

 

Here we see the all records from right table i.e. Student_details table and only matched records from left table Student as other records it’s showing null . This is Right Join.

 

 

Note : Here we use a and b as alias for tables to our convenient as it’s easy to use in condition . We can also directly use table name instead of alias but it’s better to use alias if we join n number of tables then it’s easy for us to put condition via alias.

 

 

 

5 .  Full Join : Full Join used to get all the records from left and right table

 

Syntax:

 

SELECT

    a.id id,

    a.name,

    b.id,

    b.address

FROM

    student a

FULL OUTER JOIN  student_details b ON a.id = b.id;

 

Here we get all the records from with the matching rows from both tables available. If it’s no match, the missing  will have nulls values.

 

 

Note : Here we use a and b as alias for tables to our convenient as it’s easy to use in condition . We can also directly use table name instead of alias but it’s better to use alias if we join n number of tables then it’s easy for us to put condition via alias.

 

6 . Cross Join :  If we take records from both tables and will not put any condition that is known as Cross Join . Also known as Cartesian Product as we not given any condition so it will multiply the records from Table A and Table B.

 

Syntax and Output :

 

mysql> select a.id , a.name, b.id , b.address from student a , student_details b;

+----+----------+------+---------+

| id | name     | id   | address |

+----+----------+------+---------+

|  1 | Ashish   |    1 | MDB     |

|  2 | Prashant |    1 | MDB     |

|  3 | Deepak   |    1 | MDB     |

|  4 | Krishna  |    1 | MDB     |

|  1 | Ashish   |    2 | DBG     |

|  2 | Prashant |    2 | DBG     |

|  3 | Deepak   |    2 | DBG     |

|  4 | Krishna  |    2 | DBG     |

|  1 | Ashish   |    5 | PAT     |

|  2 | Prashant |    5 | PAT     |

|  3 | Deepak   |    5 | PAT     |

|  4 | Krishna  |    5 | PAT     |

|  1 | Ashish   |    6 | MZB     |

|  2 | Prashant |    6 | MZB     |

|  3 | Deepak   |    6 | MZB     |

|  4 | Krishna  |    6 | MZB     |

+----+----------+------+---------+

16 rows in set (0.00 sec)

 

As we not put any condition in select statement simply take table so output is 4 * 4 =16 as in both table record is 4.

 

 

Note : Here we use a and b as alias for tables to our convenient as it’s easy to use in condition . We can also directly use table name instead of alias but it’s better to use alias if we join n number of tables then it’s easy for us to put condition via alias.

 

Important : Why Join prefers over Sub query. Main advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query

 

 

 

Note : If you install My SQL in your System Varchar2 will not support so you can simply use Varchar instead of Varchar2 . Varchar2 supports in Oracle , Sybase , DB2 etc database . Since My SQL is free ware so for learning you can use varchar.

 

 

Please go through this blog carefully and understand why Join is so important in SQL and also all the types joins and it’s uses in SQL  if you have any issue let me know. In next blog I will come with different  topic. I suggest you to use Joins in more than 2 tables to get the result as per your requirement. As mentioned in Blog Join is also faster than sub query.  This blog is important for candidate who is preparing for SQL interview as lot’s of question will be from join topic.

 

Thanks.