611 views

Consider the following Relation $\text{employee}$:
$$\begin{array}{|l|l|l|} \hline \text{eno} & \text{ename} & \text{manager} \\ \hline 1 & \text{satish} & \text{rajeev} \\ 2 & \text{naveen} & {} \\ \hline 3 & \text{murali} & \text{rajeev} \\ \hline 4 & \text{srinu} & {} \\ \hline 5 & \text{brahma} & {} \\ \hline \end{array}$$
How many number of tuples will be output after executing the following query?

SELECT DISTINCT(manager) FROM  employee;

1. $1$
2. $2$
3. $4$
4. $5$

Here number of distinct manager is $2$ because $1$ is rajeev and another one is NULL. In sql NULL is treated as one distinct element.

@Applied Course Every null is considered as distinct in SQL. So ans will be 4.

For the DISTINCT keyword, null values are considered to be duplicates of each other. When DISTINCT is included in a SELECT statement, only one NULL is returned in the results, regardless of how many null values are encountered.

DISTINCT, UNION, and INTERSECT always returns at most one NULL even though NULL is not equal to NULL.

When the value of an attribute isn't specified, it takes the value "NULL".

NULL indicates that the value might be absent for the attribute, or if present, is unknown. (Hence a NULL == some other NULL isn't True)

But the DISTINCT keyword distinguishes by text, and not values. So,

• NULL and NULL would not be considered distinct.

• Rajeev and rajeev would've been considered distinct (DISTINCT isn't case sensitive by default)

Here, we have two distinct values for the managers "attribute" — rajeev and NULL.

So, 2