Understanding 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.
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.
E N D
Presentation Transcript
Joins, views, and subqueries CMSC 461 Michael Wilson
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?
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
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
Join types INNER JOIN FULL OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN CROSS JOIN
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
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
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
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
Cross join Cross joins have no join condition Cross joins literally return the Cartesian product (discussed in the relational algebra slides)
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
Explicit join SELECT * FROM AddressBook a INNER JOIN CallList c on a.id = c.addressBookId;
Implicit join SELECT * FROM AddressBook a, CallList c WHERE a.id = c.addressBookId;
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
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
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
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>
View example CREATE VIEW phoneNumbers AS SELECT phoneNumber FROM AddressBook; This would create a view that only contained phone numbers from our AddressBook
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
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
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>
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
Subquery SELECT * FROM Stalkers s, (SELECT * FROM AddressBook a INNER JOIN CallList c on a.id = c.addressBookId) c WHERE s.contactName = c.contactName;
Using subqueries in a from SELECT * FROM UserProfile WHERE userId = (SELECT userId FROM MostPopularUser ORDER BY popularity LIMIT 1);
