Interview Questions   Tutorials   Discussions   Programs   

DB2 - What is JOIN and different types of JOIN?




363
views
asked mar August 20, 2014 11:22 AM  

What is JOIN and different types of JOIN?


           

1 Answers



 
answered By Mswami   0  
A join combines fields in two or more tables by matching values. If a specified value in one table matches the corresponding value in another table, each row is combined and selected.

The SQL JOIN clause is used whenever we have to select data from 2 or more tables.

To be able to use SQL JOIN clause to extract data from 2 (or more) tables, we need a relationship between certain columns in these tables.

Consider the following SQL statement:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName
The SQL expression above will select all distinct customers (their first and last names) and the total respective amount of dollars they have spent.The SQL JOIN condition has been specified after the SQL WHERE clause and says that the 2 tables have to be matched by their respective CustomerID columns.

The SQL statement above can be re-written using the SQL JOIN clause like this:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

There are 2 types of SQL JOINS – INNER JOINS and OUTER JOINS. If you don't put INNER or OUTER keywords in front of the SQL JOIN keyword, then INNER JOIN is used. In short "INNER JOIN" = "JOIN" (note that different databases have different syntax for their JOIN clauses).

The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. In case we have a customer in the Customers table, which still hasn't made any orders (there are no entries for this customer in the Sales table), this customer will not be listed in the result of our SQL query above.

But what if you want to display all the customers and their sales, no matter if they have ordered something or not? We’ll do that with the help of SQL OUTER JOIN clause.

The second type of SQL JOIN is called SQL OUTER JOIN and it has 2 sub-types called LEFT OUTER JOIN and RIGHT OUTER JOIN.

The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

 If we slightly modify our last SQL statement to:
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers LEFT JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).


flag   
   add comment

Your answer

Join with account you already have

FF

Preview

 Write A Tutorials
Online-Classroom Classes
www.writeabc.com


  1 person following this question

  1 person following this tag

  Question tags

Asked 2 years and 3 months ago ago
Number of Views -363
Number of Answers -1
Last updated
3 years and 3 months ago ago

Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert