Joins in SQL

Joins

The Join Scenario

  • All the data is not arranged in a single table
  • Relevant homogenous data is stored in corresponding tables
  • Order Details data has productCode
  • Products data has product details for a given productCode.
  • There are many tables in the database which has some connection with some other table. The information what we need may be present in homogenous tables.
  • Can we attach all the matching product details(from product table) to order details?

Right Outer Join

  • Resultant table will have all the order details(the right hand side table) and the matching product details (left hand side table)
  • All from right table, matching from left table .
  • Matching will be done based on a unique id.
  • This is called right outer join
  • Matching records will be populated , otherwise fields are left blank
  • This will be the resultant table. It will contain all the columns from both the tables. It will contain all the products from the right table i.e., the order details table but it will contain the description of only matching products from the product details table.
Resultant table
  • Number of rows in resultant table = number of rows in the right hand side table
  • Number of columns in resultant table = columns of table1 + columns of table2
  • Matching should be on product id.
Code Right Outer Join
        select * 
        from classicmodels.products 
        right outer join classicmodels.orderdetails 
        on products.productCode=orderdetails.productCode;
  • The poductCode column will be there in both the table, based on which we will find the match.

Left Outer Join

  • Same logic as right outer join
  • We would like to keep all the data from left hand side table and matching data from right hand side table
  • The table of importance is the left side one
Code – Left outer join
        select * 
        from classicmodels.orderdetails
        left outer join classicmodels.products
        on orderdetails.productCode=products.productCode;

Inner Join

  • We have two tables
  • Two tables have connecting key
  • We want the details of all the matching records or common records from two tables
  • We match the key, if it is there in both the tables then the resultant table will have the record, if it is not present in any one of the tables, then it will be dropped.

Inner Join and outer join difference

  • Left outer join contains all the records from left dataset, unconditionally
  • Right outer join contains all the records from left dataset, unconditionally
  • Inner join contains only matching records

Lab – Inner Join

  • Take motor_cycle_table. It was a small subset of products, created by taking productLine=Motorcycle
  • Inner join motor_cycle_table table and orderDetails table to see the common records.
Code – Inner Join
        select * 
        from classicmodels.orderdetails 
        inner join classicmodels.motor_cycles_table 
        On orderdetails.productCode=motor_cycles_table.productCode;

Full Outer Join

  • We have two tables.
  • Table-1 has some unique data that is not there in table-2
  • Table-2 has some unique data that is not there in table-1
  • The common data can be extracted using inner join
  • How to extract over-all available data, which is there in table-1 and table-2
  • Full outer join fetches all the available information
Code-Full Outer Join

Full outer join – Part-1 : Left Outer Join

    select * from classicmodels.orderdetails left outer join classicmodels.products on              orderdetails.productCode=products.productCode;

Full outer join – Part-2 : Right Outer Join

        select * from classicmodels.orderdetails right outer join classicmodels.products on products.productCode=orderdetails.productCode;

Then combine these tqo queries

        select * from classicmodels.orderdetails left outer join classicmodels.products on orderdetails.productCode=products.productCode
        Union
        select * from classicmodels.orderdetails right outer join classicmodels.products on products.productCode=orderdetails.productCode;

Conclusion

  • In this session we started with basics of RDBMS
  • This is a very basic class on SQL
  • SQL has many more commands for data handling and database management
  • We focused only on DML – Data manipulation language we discussed nothing about DDL – Data Definition Language
  • You may want to explore advanced functionalities of SQL while you are performing basic descriptive statistics and ad hock analysis.


 

About admin

Check Also

sql

Introduction to SQL- Basics

  SQL Basics Contents SQL Databases Sql Basics RMySql Sql Queries Sql Joins What is …

Leave a Reply

Your email address will not be published. Required fields are marked *