SELECT * call in primary key versus attribute in SQL
I am currently participating in the final and wondering about one of the problems of the practice.
The task states:
Consider the following two SQL queries on a relation R(A,B)
where the attribute A
is the primary key.
Request 1: SELECT * FROM R WHERE B > 50 OR B <= 50
Request 2: SELECT * FROM R WHERE A > 50 OR A <= 50
What is right?
and. Queries 1 and 2 return all records inR
b. Query 2 returns all records in R
, but Query 1 may not
The official answer says b is correct, but I wanted to know why. My understanding is that the primary key must be unique, i.e. Query 2 must return every record. But wouldn't query 1 also return every record, even if there were duplicate values B
?
source to share
The correct answer is the second one because A is the primary key and the primary key cannot be null, so
A > 50 OR A <= 50
will always be true and the following:
B > 50 OR B <= 50
can be NULL if B is NULL
NULL > 50 OR NULL <= 50 ---> NULL
and strings when B is null will not be returned.
source to share
The only reason I can understand why Query 1 will not return some of the records is if the column (or attribute as you call it) is NULL. When comparing null to any value in any rdbms, I know that you will never get correct results as it null
is unknown by definition. for example, if you add a where clause and null = null
, you won't get any results.
Thus, any record where B is null will not be returned from the first request.
This cannot be the case for column A because it is the primary key and the primary key cannot be nullable by definition.
source to share