Today we explore the simply quite simple and interesting concept of SQL joins.
Join is a clause which is used to combine rows from two or more tables based on a common field between/among them.
The most common join is INNER JOIN, before we start up, lets create two tables as shown below:
To clear the screen in SQL ORACLE type: cl scr(press Enter).
Now lets insert some data in these tables as shown below:
Similarly add some data into orders table as shown:
Now I recapitulate again that we have two tables named “ORDERS” and “CUSTOMERS”. I retrieve the data from two tables but use single from clause thats because of joins. I retrieve OrderId from order table aliases as O, CustomerName from Customers table aliases as C and O.Orderdate as shown:
The output which we got was based on the constraint customerid is equal in both tables(CUSTOMERS,ORDERS). When the constraint is satisfied the CUSTOMERS Table fields are populated.
What else would happen if I dont specify Inner in select query which does produces the same output as above shown. We conclude that both INNER JOIN AND JOIN ARE SAME.
If we replace * instead of o.orderid,c.customer and o.orderdate then we get the result set as:
It returns all combination of orders tables rows and customers tables rows based on constraint match.
INNER JOIN: Returns all rows when there is at least one match in BOTH tables.It means in first we got 3 rows as output now we get these 3 rows two times as we joined the two tables.
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.Observe the following screen:
One thing to note is that left join returns all rows from left table and returns only matched rows from right table.In some databases LEFT JOIN is called LEFT OUTER JOIN.
The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.
In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL FULL OUTER JOIN Keyword