SQL Question

[Bassmansam]

Hey guys

Following an SQL course and i have a query and it hasn't been covered by the teacher.

I understand this sub query is using finding pairs, in this case 100 & 20 and 101 & 30. I understand that but, whats the point of having the WHERE clause of 01-jan-2015 if it its going to ignore it and pick a pair which has the date of 2/9/15?

What's the point?

edit: forgot image link

https://ibb.co/HhNyPGK

 

[chroniclard]

Its saying give me a list of data from the sales table where the product id and unit price are in this sub query. Its not ignoring it, its filtering the sub query.

 

[bremen1874]

It's just an example, and examples often aren't that useful in the real world.

You've got to filter that query by something for it to do anything useful as an example. If there wasn't a where clause the result would be every row in the table.

 

[Bassmansam]

Its saying give me a list of data from the sales table where the product id and unit price are in this sub query. Its not ignoring it, its filtering the sub query.

Thanks.

If i wanted to return the result omitting the 2/9/15 row what would need to be changed?

edit: Just run the sub query on its own i guess?

 

[rhysduck]

I understand this sub query is using finding pairs, in this case 100 & 20 and 101 & 30.

Umm no not really, that's what the MAIN query is doing...
The subquery is finding ALL rows with a product_id and unit_price where sales_date is 01/01/15.
The main query is then using this data to filter the main query data columns.

 

[bremen1874]

Thanks.

If i wanted to return the result omitting the 2/9/15 row what would need to be changed?

edit: Just run the sub query on its own i guess?

Yes. You wouldn't need to do a comparison like this if you just wanted to filter by date.

 

[rhysduck]

Thanks.

If i wanted to return the result omitting the 2/9/15 row what would need to be changed?

edit: Just run the sub query on its own i guess?

No. As you'd then the sub query would be returning nothing.
Add an AND clause to the end of the query:

AND sales_date <> '02-SEP-2015'

 

[Bassmansam]

Umm no not really, that's what the MAIN query is doing...
The subquery is finding ALL rows with a product_id and unit_price where sales_date is 01/01/15.
The main query is then using this data to filter the main query data columns.

Well its not strictly only finding where its 01/01/15 is it

 

[rhysduck]

Well its not strictly only finding where its 01/01/15 is it

The sub query is....

 

[bremen1874]

You could do

Select *
from sales
where(product_id, unit_price, sales_date) in
select(product_id, unit_price, sales_date from sales where sales_date = '01-jan-2015')

There's obviously no point doing it that way, but as an example it's okay.

 

[Bassmansam]

Ahhh i’ve got it, the clue is in the name - Pairwise comparison. It finds the any rows that matches the date and then any other rows where the pairs match.

ok fine but as someone said thats a poor example if you only want to return sales on that specific date.

thanks all

 

[HungryHippos]

Sometimes with SQL you need to turn your thinking slightly upside down.

The query runs as a whole, rather than in the query order. SQL is pretty good at calculating the best way to optimise how the query is handled in the background.

That subquery is basically saying "I want all of the rows/data that matches the product_id and unit_price of sales dated 01-jan-2015".