Non-Correlated Subqueries: SQL Tutorial

Tim Buchalka's Learn Programming Academy
A free video tutorial from Tim Buchalka's Learn Programming Academy
Professional Programmers and Teachers - 1.1M students
4.5 instructor rating • 52 courses • 1,161,218 students

Lecture description

Uncorrelated Subqueries: Part 1

Learn more from the full course

SQL for Beginners: Learn SQL using MySQL and Database Design

Understand SQL using the MySQL database. Learn Database Design and Data Analysis with Normalization and Relationships

07:51:05 of on-demand video • Updated January 2021

  • Have a detailed knowledge and understanding of using MySQL, one of the most widely used databases in the world.
  • Obtained a knowledge of SQL, and MySQL, allows students to apply for jobs in the IT sector.
  • In-depth knowledge of database design will give students an edge over other applicants.
English [Auto] A case let's take a look non correlated queries in the WHERE clause. So you can see we have our example from the previous video. We have select Id start time from screenings where film ID is in selects ID from films where length man is greater than 120 so we have all in a query and then we have to query. So let's run this query and you can see its returned ID and start time from the screenings table where the film ID is in this in a query. So the outer query has taken the results that were produced from the in a query and has used them to select the ID and start time from the screenings table. So for non-call correlated queries we can run the query independently. So let's do that now. So let's copy and paste this in a query. And tidy up a bit and with a semi-colon and select ID from films where length is greater than one hundred and twenty. So this is all in a query in this query. I'm going to run independently now and you can see returns all the IDs from the films table where length man is greater than 120. So the query has returned to Foom ID 1 3 8 and 11. So the outer query selects Id start time from screenings with film ID is in is taking these are zos. So this query here is the same as running select Id start time from screenings where film ID is in 1 3 8 and 11. So when we run this query we can see its returned ID and start time from screenings where film ID is in 1 3 8 and 11. So this is well known correlated subquery. Do when you use them in where close. And now this right so query in those wet clothes from scratch. So here we have two tables. We have select staff and customers and select stuff from bookings so that select stuff from customers. And basically what I want to do is I want to return the first name the last name on the email from the customers table but I want to return them where the screening ID from the bookings table is equal to 1. So less ROI. So query for this. So in the next time you can Rice select its first name last name and email from customers and then you can say where ID. So the ID field in the Customers table is in and then we can write in a query. So we want to select the customer ID from the bookings table. So select customer ID from bookings and then we want to say where the screening ID is equal to 1. So the screening ID Decoud to 1 and then we can run this query. So we have our query in a query so we can see these are the three customers that made a booking for a screening ID one. So we have John Smith and his email address. We have cherry wine and we have Simon Davis. So again let's run in a query on its own. So without the equerry and with a semi kodo and you can see it returns three customer IDs for 12 and 16. So it returns free values for the select first name last name and email from customers where ID is in for 12 and 16. So when we run the whole query together we get the first name the last name and email for Customer ID for 12 and 16. And basically we can do this because here we've got the ID field in the Customers table and we have the customer ID in the booking table and if you remember the customer ID and the bookings table is a foreign key to the ID field and the Customers table. So we can make the link between the two tables in this query and its the same for the film ID and the screenings table and the ID field in the films table. So the film ID and the screenings table is a foreign key to the ID field in the films table. So again we can make this link and use some query to link the two tables case to us for the correlated queries in the WHERE clause. Now let's take a look at nung correlate queries in the from.