Joins quick reference


Basics

Employee

 Empid
 Name
 courseid
 1
 Sandeep
 10
 2
 Deepak
 20
 3
 Dharmu








Course
 id
 courseName
 duration
 10
 mysql
 1.5
 20
 java programming
 2
 30
 String programming
 1








Inner Join or Simply Join :

select * from Employee JOIN Course ON Employee.courseid = Course.id;

Output:
this will first internally do Cartesian product from each row of left table to each row in right table,
then applies the filter , to match row, which has same courseid and id.

So this will be the output, next all joins will add few more rows on this output.
 Empid
 Name
 courseid
 id
 courseName
 duration
 1
 Sandeep
 10
 10
 mysql
 1.5
 2
 Deepak
 20
 20
 java pro
 2

rest unmatching rows from both tables will not be displayed

 Left join/ Left outer Join

In addition to above inner join output, it will also display all the rows from left tables(employee), even if there is no matching rows in right table, but NULL/empty value of right tables columns

i.e extra rows

 Empid
 Name
 courseid
 id
 courseName
 duration
 1
 Sandeep
 10
 10
 mysql
 1.5
 2
 Deepak
 20
 20
 java pro
 2
3
Dharmu
NULL
NULL
NULL
NULL

Effectively displays, the employees, who have not enrolled for any courses

Right Join/ Right Outer Join
reverse of Left

In addition to above inner join output, it will also display all the rows from right tables(course), even if there is no matching rows in left table, but NULL/empty value of right tables columns


 Empid
 Name
 courseid
 id
 courseName
 duration
 1
 Sandeep
 10
 10
 mysql
 1.5
 2
 Deepak
 20
 20
 java pro
 2
NULL
NULL
NULL
30
String pro
1


Effectively displays the courses, which did not find any takers i.e. the courses which are not enrolled by anyone

Full outer Join

In addition to above inner join output,
it will also display all the rows from left tables(employee), even if there is no matching rows in right table, but NULL/empty value of right tables columns
and
it will also display all the rows from right tables(course), even if there is no matching rows in left table, but NULL/empty value of right tables columns

 Empid
 Name
 courseid
 id
 courseName
 duration
 1
 Sandeep
 10
 10
 mysql
 1.5
 2
 Deepak
 20
 20
 java pro
 2
3
Dharmu
NULL
NULL
NULL
NULL
NULL
NULL
NULL
30
String pro
1




Comments

Post a Comment

Popular posts from this blog

Oracle User vs Oracle Schemas

Oracle schema = Oracle user + database objects owned by that user. When an Oracle user is created using the CREATE USER command, a schema gets created for the user by default with the same name as User There is a one-to-one correspondence between Oracle user name and Oracle schema name. While user = schema in most circumstances, that isn’t true all the time. CREATE SCHEMA does not actually create a schema. It only lets you create multiple tables/views and grant privileges on them in your own schema, in a single transaction. Example:  Here’s an example of using CREATE SCHEMA in the schema “hr” to create a table, create a view on that table, and give grant to another user on that view. SQL> CREATE SCHEMA AUTHORIZATION hr
Read more

Having and Where Clause

Where and Having both are used to apply filter Difference: You can not use Aggregate function in where clause, like select * from employee where SUM(salary) > 5000; but you can use Aggregate function in having clause. But having has to be used in conjunction with " GROUPED BY " clause. GROUP BY, makes the list of group, say if you have applied two " Grouped by" select city,gender   from employee   grouped by city, gender; in above case, you will have below type of  data Group-1 id  name      city      gender salary 2   Dharmu  BLR    M         4000 8   deepak    BLR   ML       5000 Group-2 id  name      city      gender salary 1   rashmi    BLR    F          10000 3   Sargun    BLR...
Read more