SQL Joins | Inner Join | Left Join | Right Join | Full Join | Self Join

-

Hello, and welcome to the blog. In this blog we are going to talk about "Joins".

In the previous blog we have seen most important topic of Primary Key, Foreign Key and now its time to join some of the tables.

So, In this blog we are going to talk about
  1. What is join and its purpose
  2. Type Of Joins
    1. Inner Join
    2. Left Join
    3. Right Join
    4. Full Join
    5. Self Join
Let's  start with

1) What is join and its purpose

  1. There is a clause in SQL called Join. And its purpose is to combine rows from two or more tables which are connected with each other by a common column.
  2. When I say connected with each other by a common column I am talking about Foreign Key which we have covered in previous blog. Although if you haven't enforced foreign key for this blog but we do have a common column then also you can perform join operation.
  3. For explaining join I have created 2 table:
    1. Employee Table: (Table Name = 'Employee1')



    2. Department Table: (Table Name = 'Department')

2) Types Of Join

  1. Inner Join

    1. Inner join is the most used join in SQL. In real life scenarios we use inner join almost 90% of time.
    2. Now let's talk about what actually happen in inner join is "It selects record that are having matching values in both table".
    3. let's see syntax and example for better understanding:
    4. Syntax is :
      SELECT column_1, column_2
      FROM table_1
      INNER JOIN table_2
      ON table_1.column_name=table_2.column_name
    5. Let's see a example
      SELECT FirstName, LastName, DepartmentName
      FROM Employee1 e
      INNER JOIN Department d
      on e.Department=d.Id

    6. Now let me explain what has happen now, we have 8 records in our 'Employee1' table out of which for 3 records 'Department column' is null, so null value doesn't represent any record from 'Department table' so inner join has eliminated those records.
    7. For remaining 5 records value of 'Department column' mentioned in 'Employee1' table is available in Department table and represent a particular record so those records got merged.

  2. Left Join

    1. The left join return all the record from the the table mentioned first and matching records from the table mentioned later. We can also portrait it as all records from table mentioned at Left side of "=" sign and matching record from right table.
    2. If there is no matching record from right hand side table then the column from right hand side will show their values as null.
    3. Let's see syntax and example to understand better
    4. Syntax
      SELECT column_name
      FROM table_1
      LEFT JOIN table_2
      ON table_1.column_name = table_2.column_name
    5. Example :
      SELECT FirstName, LastName, , DepartmentName
      FROM Employee1 e
      LEFT JOIN Department d
      ON e.Department = d.Id


    6. Records from 'Employee1' table which are having 'Department column'  value null those are represent as null all other values which match 'Id' column of 'Department table' are represented with actual values.

  3. Right Join

    1. This one is opposite of Left Join.
    2. The right join return all the record from the the table mentioned second and matching records from the table mentioned earlier. We can also potrait it as all records from table mentioned at right side of "=" sign and matching record from left table.
    3. If there is a matching record from left hand side table then the column from left hand side will show their values as null.
    4. Let's see syntax and example to understand better
    5. Syntax
      SELECT column_name
      FROM table_1
      RIGHT JOIN table_2
      ON table_1.column_name = table_2.column_name
    6. Example
      SELECT FirstName, LastName, , DepartmentName
      FROM Employee1 e
      RIGHT JOIN Department d
      ON e.Department = d.Id

    7. If we have a record in Department table not consumed by Employee1 table, then for those records values for column from 'Employee1' table is shown as null

  4. Full Join

    1. This one is a combination of left join and right join you can say.
    2. This will return all the records when there is a match in left hand side table or right hand side table.
    3. One most imporatnt thing to remeber is when you are using full join then the result-set you will get is going to be potentially large so the query will take time to get execute.
    4. Let's see synatx and example to understand it better
    5. Syntax:
      SELECT column_name
      FROM table_1
      FULL JOIN table_2
      ON table_1.coumn_name = table_2.column_name
    6. Example
      SELECT FirstName, LastName, DepartmentName
      FROM Employee1 e
      FULL JOIN Department d
      ON e.Department = d.Id

    7. As you can see we have total 9 records in our result.
    8. First 8 are from 'Emploee1' table and last one is from 'Department' table

  5. Self Join

    1. As the name suggest self join is there when we join a table to itself.
    2. You guys must be wondering what is purpose of self join and why would I need to join a table to itself and what people will achieve by doing that.
    3. I will explain you this concept with example: We have a table with following fields:
      1. Id
      2. FirstName
      3. LastName
      4. Department
      5. Salary
      6. Designation
      7. Reporting Manager
    4. In this table the Id's we have mentioned in the 'ReportingManager' column are actually pointing to a record in the same table. The '1' in the 'ReportingManager column refers to the record in 'Employee1' table with 'Id' as '1'.
    5. Now let's say I want to show Employee Name and whom they report to
    6. Now let's see a example:
      SELECT e1.FirstName, e2.FirstName as Reporting Manager
      FROM Employee1 e1
      JOIN Employee1 e2
      ON e1.ReportingManger = e2.Id

    7. To understand it more clearly let's make a select query:
      SELECT Id, FirstName, ReportingManager
      FROM Employee1

    8. So here we have Mapped value from 'ReportingManager' column to value in 'Id' column of 'Employee1' table
Now, before ending this blog I want to show one more way of performing join
Example 1:
SELECT e1.FirstName, e2.FirstName as ReportingManager
FROM employee1 e1, employee1 e2
WHERE e1.ReportingManager=e2.EmployeeId


You are getting same result. I'll show you one more example:
SELECT e.FirstName, d. DepartmentName
FROM Employee1 e, Department d
WHERE e.DepartmentId=d.DepartmentId


And here we end our joins.

Congratulation  SQL. See you  in some another blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day"

Comments

Post a Comment

Popular posts from this blog

Update, Delete and Truncate Commands

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "Updating and deleting data". We learn about "Inserting and Retrieving Data" in  this blog . Now its time to learn updating and deleting our mistakes. Yes with the help of the UPDATE and DELETE  statements  you can actually update and delete wrong entries in your database respectively. So in this blog we are going too talk about  UPDATE statement DELETE  statement TRUNCATE command Let's start with 1) UPDATE statement : UPDATE statement is used to update a specific record or a bunch of records which exists in your database table. If you made a mistake while inserting data in your database then UPDATE statement is the loophole through which you can correct it. Let's see a example: If you can see in our table I have made a mistake that SALARY  of "Sales Per...
Read more

More clauses in SQL

-
Image
Hello, and welcome to the blog. In this blog we are going to talk about "More clauses in SQL". We learn about "WHERE" clause in this blog . Now its time to learn more clause which are going to help us to be more specific about retrieving data from Database and eventually its gonna make us more and more efficient in handling data in Database. So, In this blog we are going to talk about GROUP BY clause HAVING clause ORDER BY clause Now, let's talk about 1) GROUP BY clause This clause is often used with Aggregate Functions (which we have learnt in previous blog ) so that we can group our result with a particular column. I know it sound little confusing but we will solve this confusion with the help of example. Let's consider a scenario where I told you to count number of people working in each department with our existing Employee table. So then you might write a query like this: select count(*) from Employee where Department='IT' Then you will keep rep...
Read more