Mastering SQL for Data Science: A Comprehensive Guide Day-5

Mastering SQL for Data Science: A Comprehensive Guide Day-5

SQL Joins

Sometimes we require to treat multiple table as though they were a single entity. Then a single SQL sentences can manipulate data from all the tables. To achieve this, we have to join tables. The purpose of join is to combine the    data spread across the tables. One of the powerful features of SQL is its capability together and manipulates data   from across several tables. A join is actually performed by the where clause which combines the specified rows of tables. Tables in a database can be related to each other with keys. A primary key is a column with a unique value      for each row. A join is a query that combines rows from two or more tables, views or materialized views. 

     Basically there are six different types of joins;

1. Cross Join
2. Inner Join
3. Left Join
4. Right Join
5. Full Join
6. Self Join

 1.   Cross Join: In SQL, the Cross join is used to combine each row of the first/left table with each row of the second/right table. It is also known as a Cartesian join since it returns the Cartesian product of the sets of rows from the joined tables.

No. of Rows in the result of Cross join =No. of rows in Left table* No. of rows into Right table.

Eg: select ename,dname from emp,dept;  OR       select * from emp,dept;

 2.   Inner join:

This is the most frequently used join. It retrieves the rows from two tables having a common column and is further classified into equi join and non-equi join. Equi joins are also known as inner join. There are the most common joins used in SQL. They are known as equi joins because the where statement generally compares two column from two tables with the equivalence operator (=). This type of join is by far the most commonly used.

E.g: select * from emp e,dept d where e.dept_no=d.dept_no;

                             OR

          select * from dept d inner join emp e on (d.dept_no=e.dept_no);

3.   Left Join/Left outer join:

The Left join command returns all rows from the left table, and the matching rows from the right table. The result is Null from the right side, if there is no match.

E.g: select * from dept d left join emp e on(d.dept_no=e,dept_no);

4. Right Join:

The Right join returns all records from the right table and the matching records from the left table. The result is 0 records from the left side, if there is no match.

E.g: select * from dept d right join emp e on (d.dept_no=e,dept_no);

 5.   Full Join:

Unlike an Inner join, a Full join returns all the rows from both joined tables, whether they have a matching row or not. Hence, full join is also referred to as a Full Outer join.

E.g: select * from dept d full join emp e on (d.dept_no=e,dept_no);

E.g: –display all the department in which there is no employee;

select dname from dept d left join emp e on (d.dept_no=e.dept_no) where emp_no is null;

6.  Self join:

 In some situations, we may find it necessary to join a table to itself, as though we were joining two separate tables. This is referred to as a self-join. In a self-join, two rows from the same table combine to a result row. To join a table with itself, two copies of the very same table have to be opened in memory. Hence in the from clause, the table name needs to be mentioned twice. 

Since the table names are the same, the second table will overwrite the first table and in effect, result in only one table
being in memory. This is because a table name is translated into a specific memory location. To avoid this, each table is opened using an alias. Now these table aliases will cause two identical tables to be opened in different memory locations. 

E.g: select e.ename,e1.ename from emp e,emp e1  where e.emp_no=e1.mgr_no;