Sql unit 9 correlated subqueries
Download
1 / 112

SQL Unit 9 Correlated Subqueries - PowerPoint PPT Presentation


  • 139 Views
  • Uploaded on

SQL Unit 9 Correlated Subqueries. Kirk Scott. 9.1 Correlated Subqueries 9.2 EXISTS 9.3 NOT EXISTS 9.4 Double NOT EXISTS. 9.1 Correlated Subqueries. 1. Recall how subqueries with IN work. There is an inner query and an outer query.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQL Unit 9 Correlated Subqueries' - glora


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Sql unit 9 correlated subqueries

SQL Unit 9Correlated Subqueries

Kirk Scott


Sql unit 9 correlated subqueries


9 1 correlated subqueries
9.1 Correlated Subqueries


Sql unit 9 correlated subqueries

  • 1. Recall how subqueries with IN work.

  • There is an inner query and an outer query.

  • The inner query is run first, producing a set of results.

  • The outer query is run second, and the results it produces depend a row-by-row, yes or no test, of whether a field value is in the results of the inner query.


Sql unit 9 correlated subqueries

  • A row would be included in the outer query results only if the value of one of its fields matched a value in the results of the inner query.

  • The connection between the two queries depends on the matching of the field in the WHERE clause of the outer query with the field in the SELECT clause of the inner query.


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • SELECT name correlated

  • FROM Salesperson

  • WHERE spno IN

  • (SELECT spno

  • FROM Carsale

  • WHERE Salesperson.spno = Carsale.spno)


Sql unit 9 correlated subqueries

  • You would never actually write a query like this intentionally, because the correlation in this case is redundant.

  • This query gives exactly the same results as the previous example.

  • However, the query is syntactically correct and it illustrates some of the important aspects of correlation.


Sql unit 9 correlated subqueries

  • Consider the join. intentionally, because the correlation in this case is redundant.

  • Recall that for inline views, the outer query may make use of fields defined in the inner query.

  • On the other hand, for queries with IN, the outer query can’t make use of fields defined in the inner query.


Sql unit 9 correlated subqueries

  • In a correlated intentionally, because the correlation in this case is redundant. subquery, the fields of the outer table are available to the inner query.

  • In this example, the inner query can make use of the field Salesperson.spno in the join.

  • The only thing to keep in mind is that if you are joining on fields that have the same name, then those names have to be qualified with the names of their tables in order to tell them apart.


Sql unit 9 correlated subqueries

  • There is another thing to notice about correlated intentionally, because the correlation in this case is redundant. subqueries.

  • The term correlated means that the inner and outer queries are not independent of each other.

  • Unlike other nested queries, the inner query does not run to completion first, followed by the outer query.

  • The inner query cannot run to completion independently of the outer query because the inner query contains a join which involves the outer table.


Sql unit 9 correlated subqueries

  • 3. The next example illustrates a correlated intentionally, because the correlation in this case is redundant. subquery which does not have the redundancy of the previous example.

  • This is a type of subquery with an operator which also includes correlation.

  • In words, the query will find the names of salespeople who sold at least two cars.

  • This query works by comparing the results of a correlated subquery using COUNT with the constant value 2:


Sql unit 9 correlated subqueries

  • SELECT name intentionally, because the correlation in this case is redundant.

  • FROM Salesperson

  • WHERE

  • (SELECT COUNT(*)

  • FROM Carsale

  • WHERE Salesperson.spno = Carsale.spno) >= 2


Sql unit 9 correlated subqueries

  • In order to understand what the previous query accomplishes, it might be useful to look at a query that does approximately the same thing using syntax learned earlier.

  • The following query finds the names of salespeople who sold more than two cars, and also finds the counts of the numbers of cars they sold by doing a join and using GROUP BY with HAVING:


Sql unit 9 correlated subqueries

  • SELECT name, COUNT(*) it might be useful to look at a query that does approximately the same thing using syntax learned earlier.

  • FROM Salesperson, Carsale

  • WHERE Salesperson.spno = Carsale.spno

  • GROUP BY spno HAVING COUNT(*) >= 2


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • The inline view plays the role of the table in the FROM clause of the outer query.

  • The fields of this inline view are available to the outer query.

  • Since the view is unnamed, a new field, like the COUNT, has to be given an alias if it is to be referred to elsewhere in the query.


Sql unit 9 correlated subqueries

  • The examples get a little messy. clause of the outer query.

  • It’s clear that there may be more than one way to write a query that accomplishes something more complicated than “SELECT * FROM Table”.

  • Keep in mind that the point of this section is that one of the alternative ways involves a correlated subquery.

  • The other ways which we’ve seen before are now just sideshows.


9 2 exists
9.2 EXISTS clause of the outer query.


Sql unit 9 correlated subqueries

  • 1. The most common use of correlated clause of the outer query. subqueries is with the keyword EXISTS.

  • This is the general form of an EXISTS query:

  • SELECT blah

  • FROM blah

  • WHERE EXISTS

  • (A correlated subquery goes here.

  • A join is needed to connect the inner and outer queries.)


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • 2. Suppose you would like to find the names of salespeople where there exist sales records for those salespeople in the Carsale table.

  • This would be the solution using the keyword EXISTS:

  • SELECT name

  • FROM Salesperson

  • WHERE EXISTS

  • (SELECT *

  • FROM Carsale

  • WHERE Salesperson.spno = Carsale.spno)


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • The inner query does a SELECT *. the inner and the outer query.

  • There is no need to match fields from the inner and outer table in the SELECT, so you can select anything you want to. SELECT * is used because you only have to type the one symbol, *.

  • It suggests whole records from the Carsale table, but keep in mind that none of the fields are actually needed in the query.


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • None of the alternatives is either better or worse than the others.

  • It's personal choice whether to use EXISTS, IN, or a join.

  • Remember that with the subqueries it's not possible to select fields from the inner table to appear as results in the outer query.

  • If you want to do that, then you have to use a join.


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • SELECT query. vin, make, model, year

  • FROM Car

  • WHERE EXISTS

  • (SELECT *

  • FROM Carsale

  • WHERE Car.vin = Carsale.vin)


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • Recall that IN was a set operator. error, but it is wrong.

  • This example highlights the fact that EXISTS is a kind of logical operator.

  • Either something exists or it doesn't, either "EXISTS" is true or it's false.

  • Without the correlation, either Carsale contains records or it doesn't.


Sql unit 9 correlated subqueries

  • If it does, then the inner query is true, and the outer query will simply select the names of all salespeople.

  • If, by chance, the Carsale table contained no records, then the inner query would return false, and the outer query would return nothing, even though there may be salespeople in the Salesperson table.


Sql unit 9 correlated subqueries

  • Remember that a join query without a joining condition gives drastically wrong results

  • —namely a Cartesian product.

  • An EXISTS query without a joining condition, that is, without correlation, does not give such drastically wrong results.

  • But if written without a join it is almost certainly not the query that was intended.


Sql unit 9 correlated subqueries

  • 6. Just as it was possible to join a table with itself, it's also possible to write a correlated subquery with EXISTS where the table of the inner query is the same as the table of the outer query.

  • This example will be based on the Salesperson table.

  • Here are its contents again, as a reminder:


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • SELECT * in the query.

  • FROM Salesperson AS A

  • WHERE EXISTS

  • (SELECT *

  • FROM Salesperson AS B

  • WHERE A.spno = B.bossno)


Sql unit 9 correlated subqueries

  • Looking back at the table contents, it's easy to see that the only salesperson who is a boss is Bugs Bunny.

  • Looking at the logic of the query, spno 333, Bugs Bunny's spno, is the only spno where there are other salesperson records in the table that have this value as their bossno.

  • As pointed out with an earlier example, in general, it would also be possible to do this query using IN or a join.


Sql unit 9 correlated subqueries

  • 7. The last example of a plain EXISTS query is slightly less realistic than some of the others, but it does give a chance to use a non-equijoin as the correlating condition.

  • Verbally, this query could be described as finding those cars that are so cheap that their stickerprices are lower than the dealercosts of some other cars in the Car table.


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • SELECT * table, is opened twice and has to be aliased.

  • FROM Car AS A

  • WHERE EXISTS

  • (SELECT *

  • FROM Car AS B

  • WHERE A.stickerprice < B.dealercost)


9 3 not exists
9.3 NOT EXISTS table, is opened twice and has to be aliased.


Sql unit 9 correlated subqueries

  • 1. NOT EXISTS is the negation of EXISTS and its meaning is straightforward.

  • If you think back to NOT IN, if IN produced a certain set of results, then NOT IN would produce the complement of that set.

  • Similarly, if EXISTS would hold true for a certain set of tuples, then NOT EXISTS would hold false for that set.


Sql unit 9 correlated subqueries

  • Put another way, NOT EXISTS would specify those straightforward. tuples for which the condition did not hold true.

  • If you wanted to find the names of salespeople who didn't sell a car, if there were any that didn't sell a car, you could do it this way:


Sql unit 9 correlated subqueries

  • SELECT name straightforward.

  • FROM Salesperson

  • WHERE NOT EXISTS

  • (SELECT *

  • FROM Carsale

  • WHERE Salesperson.spno = Carsale.spno)


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • SELECT query. vin, make, model, year

  • FROM Car

  • WHERE NOT EXISTS

  • (SELECT *

  • FROM Carsale

  • WHERE Car.vin = Carsale.vin)



Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • 1. Queries that are doubly nested with NOT EXISTS are the most logically complex queries that will be dealt with here.

  • In theory you could nest even deeper than that, but it is difficult to understand beyond double nesting, and it's hard to think of a use for such complicated queries.

  • Although not simple, doubly nested NOT EXISTS queries do have a specific and useful meaning.


Sql unit 9 correlated subqueries

  • The keyword EXISTS in SQL corresponds to what is known as the existential quantifier in mathematical logic.

  • In a logical proposition, EXISTS is signified by the phrase "there exists".

  • It addresses the question of whether or not there exists at least one case where a particular condition holds.


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • For better or worse, doubly nested NOT EXISTS queries are the classic way to accomplish universal quantification in SQL.

  • You may recall that in analyzing the results of nested IN queries, it frequently came down to a question of, “Are all included or are some included?”

  • This means that universal quantification can also be accomplished with IN/NOT IN queries


Sql unit 9 correlated subqueries

  • The reality is that doubly nested NOT EXISTS may be the most straightforward way of dealing with the question of all vs. not all.

  • If you prefer to try and deal with this using IN queries, that’s OK.

  • You will need to know how to write both IN and EXISTS queries.

  • On assignments or tests, if a question is specifically about the concept of “for all”, it is highly likely that the clearest route to a solution will be double NOT EXISTS.


Sql unit 9 correlated subqueries

  • The logical equivalence of "for all" and double NOT EXISTS will not be proven.

  • Here is a verbal explanation of the equivalence:

  • Suppose I am interested in this proposition:

  • "Condition X holds in all cases."

  • I claim that this proposition is equivalent:

  • "There are no cases in which condition X does not hold."


Sql unit 9 correlated subqueries

  • Both in math and the English language "two negatives yield a positive".

  • На русском языке это не совсем так.

  • You're warned in English class not to write sentences with double negatives because they are hard to understand.

  • They are also hard to understand in SQL, but you have no choice.


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • D. In a simple example, the table of the outer query is joined to the table of the innermost query, and the table of the middle query is also joined to the table of the innermost query.

    • Needless to say, if this plan is to be realized, there have to be corresponding fields in the respective tables.



Sql unit 9 correlated subqueries

  • 3. Before giving a doubly nested NOT EXISTS query, a reminder of the relationships between the Customer, Carsale, and Salesperson tables is given here in the form of a straightforward three-way join:

  • SELECT Customer.name, salesprice, Salesperson.name

  • FROM Customer, Carsale, Salesperson

  • WHERE Customer.custno = Carsale.custno

  • AND Carsale.spno = Salesperson.spno


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • Put another way: describe it in the following way.

  • You're interested in the customers where there is no salesperson that the customers haven't bought a car from.

  • Enough word twisting.

  • The query follows.


Sql unit 9 correlated subqueries

  • SELECT * describe it in the following way.

  • FROM Customer

  • WHERE NOT EXISTS

  • (SELECT *

  • FROM Salesperson

  • WHERE NOT EXISTS

  • (SELECT *

  • FROM Carsale

  • WHERE Customer.custno = Carsale.custno

  • AND Carsale.spno = Salesperson.spno))


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • The two joining conditions that link the three tables together on their corresponding fields are in the innermost query.

  • The logic of the query as seen when reading its parts in order follows the logic of the doubly negated expression of the meaning of the query in the English language.


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • There is another approach to analyzing and understanding a query like this.

  • In general, a doubly nested query can involve three tables.

  • Those three tables are likely to consist of one entity in a many-to-many relationship with another, with a table in the middle.

  • An E-R diagram of this situation follows:


Sql unit 9 correlated subqueries

Entity1 query like this.

Table in the middle

Entity2


Sql unit 9 correlated subqueries

  • The important thing to notice about the structure of the double NOT EXISTS query is this:

  • Reading from left to right in the diagram, you have Entity 1, Table in the middle, Entity 2.

  • The order that the tables appear in the query is Entity 1, Entity 2, Table in the middle.

  • The two joining conditions, which both involve the Table in the middle, occur in the innermost subquery, along with the Table in the middle.


Sql unit 9 correlated subqueries

  • 5. It is easy to make another example by turning the previous query around.

  • Suppose you'd like to find the salespeople who have sold cars to all customers.

  • Again, there are various ways to rephrase it.

  • One way which is close to the logic of the SQL query is:

  • Find the salespeople where there does not exist a customer that the salesperson hasn't sold a car to.


Sql unit 9 correlated subqueries

  • It should be apparent that the order of tables in the query makes a big difference.

  • Turning the query around logically results in the tables appearing in this query in the opposite order to their appearance in the previous one.

  • The joining conditions remain the same.

  • Here is the SQL for this query:


Sql unit 9 correlated subqueries

  • SELECT * makes a big difference.

  • FROM Salesperson

  • WHERE NOT EXISTS

  • (SELECT *

  • FROM Customer

  • WHERE NOT EXISTS

  • (SELECT *

  • FROM Carsale

  • WHERE Salesperson.spno = Carsale.spno

  • AND Carsale.custno = Customer.custno))


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • SELECT * resulting in a different query, which illustrates other things.

  • FROM Customer

  • WHERE NOT EXISTS

  • (SELECT *

  • FROM Carsale

  • WHERE Customer.custno = Carsale.custno

  • AND NOT EXISTS

  • (SELECT *

  • FROM Salesperson

  • WHERE Carsale.spno = Salesperson.spno))


Sql unit 9 correlated subqueries

  • There are several points to be made about this query. resulting in a different query, which illustrates other things.

  • The first point is syntactical.

  • Unlike the previous examples, one of the joining conditions is in the first subquery and the other is in the second subquery.

  • This query won't work in Microsoft Access SQL if the condition in the first subquery is put together with the condition in the second subquery.


Sql unit 9 correlated subqueries

  • Look at the tables involved in the first condition: resulting in a different query, which illustrates other things.

  • They are Customer and Carsale.

  • If this condition is moved into the second subquery, it will be inside a nest where neither of these tables exist.

  • This doesn't cause an error message to be generated, but this condition will be ignored.


Sql unit 9 correlated subqueries

  • The second point has to do with the meaning of the query. resulting in a different query, which illustrates other things.

  • In words, it might be translated as:

  • Find the customers where there is no carsale to that customer where there isn't a salesperson for the carsale.

  • In other words, find the customers where all of the purchases they made were made from salespeople.


Sql unit 9 correlated subqueries

  • Unlike the previous examples, you would expect to get results from this query since you would expect most customers to have made their purchases from salespeople.

  • Depending on the data in the database, there may be some customer who at some time has bought a car without a salesperson being recorded for the sale.

  • If so, that customer would not be in the results of the query.


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

Entity1 of the tables in the query agrees with the order of the table, reading form left to right in the diagram.

Table in the middle

Entity2


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • That leads to the third point. meaning of the query is drastically changed.

  • It is possible to write queries with both EXISTS and NOT EXISTS.

  • Suppose you wanted to find any customers who had made at least one purchase without a salesperson

  • Suppose you also wanted to exclude those customers who only made purchases from salespeople


Sql unit 9 correlated subqueries


Sql unit 9 correlated subqueries

  • SELECT * first

  • FROM Customer

  • WHERE EXISTS

  • (SELECT *

  • FROM Carsale

  • WHERE Customer.custno = Carsale.custno

  • AND NOT EXISTS

  • (SELECT *

  • FROM Salesperson

  • WHERE Carsale.spno = Salesperson.spno))


Sql unit 9 correlated subqueries

  • In general, these queries can be arbitrarily complex. first

  • There can be both EXISTS and NOT EXISTS;

  • the joining conditions may be equality or not equality;

  • you can nest more than two levels, and so on.

  • The challenge comes from understanding what these arbitrarily complex queries mean.


Sql unit 9 correlated subqueries

  • 7. Here is one last example. first

  • It is of less practical consequence than the previous examples, but it illustrates some useful points.

  • If you understand how it works, it is a sign that you understand the relationships between the different subqueries of a double NOT EXISTS query.


Sql unit 9 correlated subqueries

  • Suppose you are interested in finding out information similar to one of the previous queries, such as customers who bought from all salespeople.

  • Suppose however, that you don't need the names of those customers in the results, just their custno's.

  • All of the information needed for the query is in one table, the Carsale table, but it would have to be opened three times, with a different alias for each of its three appearances in the query.


Sql unit 9 correlated subqueries

  • One time it would be opened because it contained customer information, another time because it contained salesperson information, and the last time because it contained information about car sales.

  • The only way you can tell what role an opening of a table is playing in the query is by which of its fields is used in the joining condition.

  • Here is an example:


Sql unit 9 correlated subqueries

  • SELECT information, another time because it contained salesperson information, and the last time because it contained information about car sales. custno

  • FROM Carsale AS A

  • WHERE NOT EXISTS

  • (SELECT *

  • FROM Carsale AS B

  • WHERE NOT EXISTS

  • (SELECT *

  • FROM Carsale AS C

  • WHERE A.custno = C.custno

  • AND B.spno = C.spno))


Sql unit 9 correlated subqueries

  • Carsale information, another time because it contained salesperson information, and the last time because it contained information about car sales. opened as A is playing the role of the Customer table.

  • You know this not just because custno is selected from it, but because of the joining condition A.custno = C.custno.

  • The joining field from A is the field which relates to customers, custno.


Sql unit 9 correlated subqueries

  • Carsale information, another time because it contained salesperson information, and the last time because it contained information about car sales. opened as B is playing the role of the Salesperson table.

  • You know this because the joining field from B is the field which relates to salespeople, spno.


Sql unit 9 correlated subqueries

  • Carsale information, another time because it contained salesperson information, and the last time because it contained information about car sales. opened as C is playing the role of itself, namely the table in the middle of the many-to-many relationship between customers and salespeople.

  • You know this because C appears in both of the joining conditions, once on custno and once on spno.

  • This query finds the custno's of customers who have bought a car from all of the salespeople.


Sql unit 9 correlated subqueries

  • There is one last point to be made about this example. information, another time because it contained salesperson information, and the last time because it contained information about car sales.

  • It is not exactly equivalent to the version of the query where the Customer and Salesperson tables were actually used.

  • This query finds those customers who bought a car from every salesperson who actually sold a car.


Sql unit 9 correlated subqueries


The end
The End car from all salespeople.