Joins, Views, and Subqueries in SQL

This informative guide delves into the essentials of joining, viewing, and utilizing subqueries in SQL. Explore multi-table selects, various join types, and examples of inner, left outer, and right outer joins, offering a clear understanding of how to manipulate and combine data in SQL queries effectively.

• SQL Joins
• Subqueries
• Database Management
• Data Manipulation

sam_pla Follow

Uploaded on Sep 15, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Joins, views, and subqueries CMSC 461 Michael Wilson

2. Multi table selects So, we mentioned that we can refer to multiple tables during the course of our selects How do we do this? What does that do for us?

3. Joins We went over the natural join before There are actually a billion types of joins you can explicitly state in ANSI SQL Okay, fine. There are 5 types. There are a lot of good images on the net about this, but I m uncomfortable with their licensing terms, so I m going to re- draw them for you

4. Joining on Most joins require you to join on a particular statement The statements are the same kind that are used in WHERE clauses Table1.id = Table2.AddressBookId The PostgreSQL refers to this as the join condition

5. Join types INNER JOIN FULL OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN CROSS JOIN

6. Inner join This is one of the more frequent types of joins Finds all rows which meet the join condition Example SELECT * FROM AddressBook a INNER JOIN CallList c on a.id = c.addressBookId; a and c are aliases

7. Inner join

8. Left outer join Contains all records between T1 and T2 that meet the join condition, and then any records in T1 that don t meet the join condition Rows for which the join condition is not met, the columns of T2 are padded with nulls

9. Left outer join

10. Right outer join Contains all records between T1 and T2 that meet the join condition, and then any records in T2 that don t meet the join condition Rows for which the join condition is not met, the columns of T1 are padded with nulls

11. Right outer join

12. Full outer join Contains all records between T1 and T2 that meet the join condition, and the combination of a left outer join and right outer join are appended onto the results

13. Full outer join

14. Cross join Cross joins have no join condition Cross joins literally return the Cartesian product (discussed in the relational algebra slides)

15. Implicit join notation vs. explicit Inner joins can be accomplished with implicit join notation In other words, we don t literally use the terms inner join in our select statement We can use where clauses/multi table selects to accomplish the same thing

16. Explicit join SELECT * FROM AddressBook a INNER JOIN CallList c on a.id = c.addressBookId;

17. Implicit join SELECT * FROM AddressBook a, CallList c WHERE a.id = c.addressBookId;

18. Which to use? Which should you use? It s up to you, really My experience I never really use explicit syntax Implicit makes much more sense to me, personally

19. Outer joins, cross joins in the real world? You can very much create a fully functional database without using left outer joins, right outer joins, etc. These types of joins allow you to collate data in ways that would be require much more data munging More queries, more tables Truth be told, I didn t realize their power until writing these slides

20. Examples Get a full list of employees and determine who has not enrolled in benefits SELECT * FROM employees LEFT OUTER JOIN benefitsEnrollment ON employees.id = benefitsEnrollment.employeeId; If an employee hasn t enrolled in benefits, the benefitsEnrollment columns would come back as NULL

21. Views A view is kind of like a virtual table Views are defined as queries (SELECT statements) They can be queried like tables They are read only Syntax: CREATE VIEW <view-name> AS <query>

22. View example CREATE VIEW phoneNumbers AS SELECT phoneNumber FROM AddressBook; This would create a view that only contained phone numbers from our AddressBook

23. More complex views CREATE VIEW callsAndInfo AS SELECT * FROM AddressBook a INNER JOIN CallList c on a.id = c.addressBookId; Views are more useful for more complex queries Can join these views on other tables as well

24. View danger! Depending too heavily on views can cause performance issues if you re not careful You can apply constraints to view queries which have a heavy performance impact If you have views that depend on views that depend on views

25. Materialized views PostgreSQL has materialized views, which are views that are stored on disk Periodically updated and stored Regular views are not stored, so they always hit dependencies in real time Syntax: CREATE MATERIALIZED VIEW <view-name> AS <query>

26. Subqueries You can use selects in your selects! This is SUPER confusing You can use them in both the FROM and the WHERE Used in the from, kind of like in-lining a view Can use them in the where to grab values from other tables

27. Subquery SELECT * FROM Stalkers s, (SELECT * FROM AddressBook a INNER JOIN CallList c on a.id = c.addressBookId) c WHERE s.contactName = c.contactName;

28. Using subqueries in a from SELECT * FROM UserProfile WHERE userId = (SELECT userId FROM MostPopularUser ORDER BY popularity LIMIT 1);