-1

An example from a book about MySql: 

SELECT vendor_id, vendor_name, vendor_state
FROM vendors
WHERE NOT EXISTS
(SELECT *
FROM invoices
WHERE vendor_id = vendors.vendor_id)

"In this example, the correlated subquery selects all invoices that have the same vendor_id value as the current vendor in the outer query. Because the subquery doesn't actually return a result set, it doesn't matter what columns are included in the SELECT clause. As a result it's customary to just code an asterisk."

The invoices table has like 10 separate columns which look like this: http://prntscr.com/h3106k

I am not fully understanding the asterisk part. Since there is 10 separate columns in this table is it not possible that some columns will be empty (or not empty) and we can check for that? There is no use of checking individual columns, and it only makes sense to check a table as a whole (so nothing else that the asterisk is needed here)?

now_m
  • 109
  • 8
  • 1
    Exists clause does not check for column it only checks whether query is returning at least one row or not. I hope this will help you https://dev.mysql.com/doc/refman/5.7/en/exists-and-not-exists-subqueries.html. – Fahad Anjum Oct 28 '17 at 07:01
  • You swapped two letters: `vendro_name` should be `vendor_name`, I think – caylee Oct 28 '17 at 07:03
  • Possible duplicate: https://stackoverflow.com/questions/14190788/subqueries-with-exists-vs-in-mysql – Tim Biegeleisen Oct 28 '17 at 07:08

1 Answers1

0

In this example, there is no row satisfying the condition (WHERE …=…). So, it is not important which column is checked as there is no row to check at all.

An alternative would be the following clause, maybe it is easier to understand:

SELECT vendor_id, vendor_name, vendor_state
FROM vendors
WHERE
    (
        SELECT COUNT(vendor_id)
        FROM invoices
        WHERE vendor_id = vendors.vendor_id
    ) = 0
caylee
  • 921
  • 6
  • 12
  • There can be a table with two columns A and B. [NOT] EXISTS checks whether a subquery returns a result set and this could be different for the column A and the column B. But with how this works, there is no way of differentiating between the two, and this is just how it works (only the * sing can be used and anything different always translates to that)? So [NOT] EXIST works only for tables as a whole and not for individual columns within them, even though it could work this way? – now_m Oct 28 '17 at 15:39
  • This is from a book that I am reading - "When you use the [NOT] EXIST operators with a subquery, it doesn't matter what columns you specify in the SELECT clause. As a result, you typically just code an asterisk". I am basically thinking that there can be situations where it can matter, but this is not true? I am just not understanding this part. I mean any cell or number of cells in a table can be empty, so this would be generating different results (if we would be looking at the columns instead of always looking at the table as a whole, with the * singn). – now_m Oct 28 '17 at 15:50
  • The answer is quite easy: Empty cells are counted too. It just matters if the cell/row exists. – caylee Oct 28 '17 at 16:32
  • Thanks for the info. So it can be said that the asterisk (*) part is based on the assumption that if one (or any) of the columns in a table exist in this subquery, all other columns would exist too. So it can be any, or all. If it is any it would be all as well.. Something like this? – now_m Oct 28 '17 at 20:05
  • Yes, I think you understand it now. If one cell in a row exists, all exist. So, you can use either an asterisk or one column's name; but the asterisk is usually shorter :) – caylee Oct 28 '17 at 21:22