1 / 16

SQL JOINS

SQL JOINS. WHY USE A JOIN?. The SQL JOIN clause is used whenever we have to select data from 2 or more tables. HOW TO USE A JOIN. To be able to use the SQL JOIN clause to extract data from 2 (or more) tables, we need a relationship between certain columns in these tables. .

toviel
Télécharger la présentation

SQL JOINS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL JOINS

  2. WHY USE A JOIN? • The SQL JOIN clause is used whenever we have to select data from 2 or more tables

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

  4. TABLES USED FOR EXAMPLES CUSTOMER TABLE: SALES TABLE: THE COMMON FIELD IS CustomerID

  5. FIRST JOIN • 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.

  6. RESULT?

  7. USE ‘JOIN’ INSTEAD OF ‘WHERE’ NOTICE THE USE OF ‘ON’ WITH THE JOIN COMMAND

  8. TWO (2) TYPES OF JOINS • Inner Join • When you use the ‘JOIN’ command , inner join is the default • Will select all rows from both tables as long as there is a match between the columns being matched

  9. INNER JOIN EXAMPLE • What if the Sales table only had the following: • And we use the same JOIN statement as before

  10. RESULT? Even though Paul and James are listed in the Customers table, they won’t be displayed because they haven’t ordered anything yet.

  11. OUTER JOIN2 SUB-TYPES • Left Outer Join • Simply used as ‘left join’ (outer is not required) • selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table.

  12. LEFT JOIN EXAMPLE • What if we wanted to list all the customers and their sales even if they haven’t placed an order? The sales table still has the following:

  13. LEFT JOIN EXAMPLE cont’d SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomerFROM Customers LEFT JOIN SalesON Customers.CustomerID = Sales.CustomerIDGROUP BY Customers.FirstName, Customers.LastName Replaced JOIN with LEFT JOIN

  14. RESULT? Everything from the Customers (first table) has been selected. For all rows from Customers, which don’t have a match in the Sales (second table), the SalesPerCustomer column has amount NULL (NULL means a column contains nothing).

  15. RIGHT OUTER JOIN • Simply used as ‘left join’ (outer is not required) • Behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement).

  16. CARTESIAN PRODUCT • If the left table has 10 rows and the right table has 18 rows then SQL Join (if not written correctly) will return 180 rows combining each record of left table with all records of right side table.

More Related