SQL joins & views

1. SQL joins & views • Intro to JOINs • SQL INNER JOIN • SQL OUTER JOIN • SQL FULL JOIN • SQL CROSS JOIN • Intro to VIEWs • Simple VIEWs • Considerations about VIEWs • VIEWs as filters • ALTER VIEW + DROP VIEW • Creating VIEWs in Management Studio • Steen Jensen, autumn 2013

2. Combining table data with joins 1 Joined in a one-to-onerelationship A JOIN joins together information from two tables into one result set A JOIN match up one record with one or more other records to make a record, that is a superset created by the combined columns of both records

3. Combining table data with joins 2 Joined in a one-to-manyrelationship

4. 4 kinds of joins • There are 4 kinds of joins: • INNER JOIN • OUTER JOIN (both LEFT and RIGHT) • FULL JOIN • CROSS JOIN • INNER JOIN is the most used • OUTER JOIN is a bit more rarely used • FULL JOIN is almost never used • CROSS JOIN is seldom used, but could be used for generating test data

5. SQL INNER JOIN 1 NB! Bogart not included INNER JOIN is the most common kind of JOIN An INNER JOIN returns only records, where there are matches for the field(s) used in the JOIN

6. SQL INNER JOIN 2 The general syntax for an INNER JOIN: In general avoid using the * operator for both tables in JOINS

7. SQL INNER JOIN 3 HumanResources.Employee Person.BusinessEntity ….. Businessentityid … ….. Businessentityid … Person.Person ….. Businessentityid … Before making a JOIN it could be a good idea to draw a diagram showing, how the tables should be connected

8. SQL INNER JOIN 4 • The INNER JOIN corresponding to the previous slide: • Also see the following links: • http://www.w3schools.com/sql/sql_join_inner.asp • http://www.w3resource.com/sql/joins/perform-an-inner-join.php

9. SQL OUTER JOIN 1 Where an INNER JOIN is exclusive by nature, an OUTER JOIN is inclusive The first named table is considered to be the left, and the second named table is considered to be the right The keyword OUTER is optional, just include either LEFT or RIGHT A LEFT OUTER JOIN includes all the information from the left table A RIGHT OUTER JOIN includes all the information from the right table E.g.: you want to see, what the special offers are for which products

10. SQL OUTER JOIN 2

11. SQL OUTER JOIN 3 • Also see the following links: • http://www.w3schools.com/sql/sql_join_left.asp • http://www.w3resource.com/sql/joins/perform-a-left-join.php

12. SQL FULL JOIN A FULL JOIN matches up data on both sides of the JOIN with everything included, no matter which side of the JOIN it is on FULL JOIN is almost never used A FULL JOIN is best described, as what you would get, if you could do a LEFT JOIN and a RIGHT JOIN in the same JOIN You get all records, that match based on the JOIN field(s) You also get any records that exist only on the left side with NULLs being returned foir columns from the right side and vice versa

13. SQL CROSS JOIN A CROSS JOIN differs from other JOINs, as there is no ON operator, and it joins every record on one side with every record on the other side of the JOIN

14. Alternative syntax for JOIN An alternative (and maybe older) version of an INNER JOIN:

15. Parts, which can be skipped Complex OUTER joins: page 106-114 Alternative OUTER / CROSS joins: page 119middle–121 UNION: page 121-125

16. Exercise in SQL JOIN Experiment making different SQL JOINS for The AdwentureWorks and/or The Amazon database Try both INNER and OUTER JOIN Before making the JOIN it might be a good idea to make a diagram with the connections between the primary and the foreign keys (see slide …)

17. Intro to views A VIEW is nothing more than a stored query You can create a simple query, that selects from only bone table and leaves some rows or columns out, or you can create a complex query that joins several tables

18. Simple views The general syntax: An example: After this the VIEW can be used just as any normal table in a SELECT

19. Considerations about VIEWs • A view is never going to run as fast, as if you had just run the underlying SELECT statement directly • VIEWs exist for a reason: security or simplification for the user – just balance your need against the extra overhead • If you want to restrict some columns for a group of people, one solution might be to make an extra table – this gives some disadvantages: • Disk space used twice • Synchronization problems, where one table is updated and the other not • Double I/O operations, as two tables have to be maintained • In this case a VIEW is a better solution

20. Views as filters 1 Just as the WHERE clause on a SELECT can filter the results of a query, so can a WHERE be used on a VIEW

21. Views as filters 2

22. More complex Views

23. Parts, which can be skipped DATEADD + CAST: page 361middle-365 WITH CHECK OPTION: page 365–367top

24. ALTER VIEW + DROP VIEW Just like the layout of a table can be changed with the ALTER TABLE, the SQL command ALTER VIEW can change the layout of a VIEW Likewise the command DROP VIEW deletes one or more VIEWs

25. Creating VIEWS in Management Studio Apart from the SQL way views can also be created the GUI way Just expand the database node and right-click Views Follow the instructions on page 368-370 in the book

26. Exercise in VIEW • Experiment making different views for The AdwentureWorks and/or The Amazon database • Try making views both the SQL way and the GUI way (see previous slide)