SQL Joins and its importance in Oracle Database #Diksha Oracle Training Centre

 

SQL Joins and its importance in Oracle Database.

 

 Hello Everyone,

Today I will discuss why SQL Joins is important in any database , Before starting Join please go through my below blogs so it’s easy for you to write different types of Joins .

https://dikshaoracletraining.blogspot.com/2020/09/sql-and-classification-of-sql-in-oracle.html

https://dikshaoracletraining.blogspot.com/2020/09/basic-sql-commands-in-oracle-database.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. Join used to 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.

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.

Below are the types of Join.

  

1.      Equi Join / Natural Join

2.      Self Join

3.      Left Outer Join

4.      Right Outer Join

5.      FULL Outer Join

6.     Cross Join / Cartesian Product

 

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

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

 CREATE  TABLE STUDENT

(ID Number PRIMARY KEY ,

 Name Varchar2(20) 

);

 Creating Second Table :

CREATE  TABLE STUDENT_DETAILS

(ID NUMBER  ,

 ADDRESS Varchar2(20)

);

Now we are inserting some dummy 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');

INSERT INTO STUDENT (ID,NAME) VALUES (5, 'Vivek');

INSERT INTO STUDENT (ID,NAME) VALUES (6, 'Sanjeev');

INSERT INTO STUDENT (ID,NAME) VALUES (7, 'Simmy');

INSERT INTO STUDENT (ID,NAME) VALUES (8, 'Smita');

-----Now Inserting dummy data in STUDENT_DETAILS TABLE

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

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

INSERT INTO STUDENT_DETAILS (ID,ADDRESS) VALUES (3, 'Delhi');

INSERT INTO STUDENT_DETAILS (ID,ADDRESS) VALUES (4, 'Noida');

INSERT INTO STUDENT_DETAILS (ID,ADDRESS) VALUES (10, 'Ahmedabad');

INSERT INTO STUDENT_DETAILS (ID,ADDRESS) VALUES (11, 'Chennai');

INSERT INTO STUDENT_DETAILS (ID,ADDRESS) VALUES (12, 'Patna');

We have inserted dummy data in both tables  values with some similar ID and  other ID so that we can see the output which matched or not matched by using several types of Join.

1.   Equi Join/Inner Join:  Equi Join is basically used to find the matched/Common records from 2 or more tables .

Syntax and output :

select a.id,a.name , b.id, b.address

from student a , student_details b

where a.id=b.id;

Output of above query is as below:

 

 

Here we can see only matched and common records as output from both the tables. Other unmatched record will not display.

Note: Here we use a and b as alias for tables for 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 by using 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 :

SELECT A.id AS ID1, B.ID AS ID2, A.name

FROM Student A, student B

WHERE A.ID <> B.ID

ORDER BY A.name;

 

Output of Above Query is as below.

 

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

Syntax and Output:

 

SELECT

         a.id id,a.name,

         b.id,b.address

     FROM

        student a

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

 

Output of the above query is as below :

 

 

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. In such type of cases we use left Outer Join.

 

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

 

SELECT

      a.id id,a.name,

      b.id,b.address

           FROM

           student a

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

Below is the output of above query :

 

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 Outer Join.

  

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

 Syntax and Output:

SELECT

    a.id id, a.name,

    b.id, b.address

FROM

    student a

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

 

Below is the output of above query.

 

 

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.

  

6 . Cross Join :  If we take records from both tables and will not put any condition that is known as Cross Join . 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. Suppose One table have 10 records and another table contains 5 records and we use cross join then output will be 10*5=50 records.

 

Syntax and Output :

 select a.id , a.name, b.id , b.address

from student a , student_details b;

 

Output of the above query is as below.

 

Oracle join is mainly used to combine columns from two or more tables based on values of the related columns. 

Note that you can use join a table to itself to query hierarchical data using an inner join, left join, or right join. Join is very important in SQL and mostly used to fetch records from multiple tables.

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 related to SQL. I suggest you to use Joins in more than 2 tables to get the result as per your requirement. This blog is important for candidate who is preparing for SQL interview as lots of question will be from SQL Join topics.

Thanks