SQL_IN_Query

Question

Answer should be $0$ row,right?

Comments

why error?

i am also getting 0 rows....

---

It will be error because in the query it us asked where manager in (null, null,null) but eid which is a primary key cannot be null.even if we consider ename as primary key, still this field cannot be null.

---

there is no primary key in this relation.We can have a relation without primary key!

---

I had misconception that a relation must have primary key.Thanks for rectifying.

---

i ran this query on WESCHOOLS by creating the same table and using the same query. ----> output : no result.

and this is same output that is shown when it does not matches to any of the values in IN.

I think it should be 0 rows because both condition gives the same output either with null or other case.

---

@arvin can u give the link?

eno generally a primary key right?

---

@srestha mam : its w3schools. what i did was i created a table than inserted 3 same records  and than ran the query...

yes eno or ename can also be considered.

---

@anand,

How a relation in RDBMS exist with out a primary key...

---

can we use unique key! inplace of pk..  ?

---

Noo. .Unique key may contains NULL but primary key doesn't have NULL

---

https://stackoverflow.com/questions/3459429/is-a-primary-key-necessary-in-sql-server

See we can create it, but creation of this is just useless

---

if we declare UNIQUE KEY NOT NULL; i think we can make this work without a primary key.

---

Then is it necessary for a relational table to have primary key? Someone please confirm.

---

@arvin

UNIQUE KEY + NOT NULL = CANDIDATE KEY

one of the candidate key choosen as PK

every candidate key is indirectly a PK

 

Why we have to declare a PK in relation externally ?

For prevention only... means by mistake we can insert Duplicates or NULL values in the Candidate Key

 

Every Table have a PK internally? ( in worst case combination of all attributes of a relation act as PK for that relation )

yes, otherwise duplicate rows are allowable in the table.

---

here i meant implementation of a unique key with  not null when we dont declare a primary key for a table as either we have to declare a key as primary key or we can manage it by declaring unique not null.

.

for a table to exist uniquely there must be a primary key so as to prevent the wastage of space to be more precise.

---

that what i am saying also.... even though you declare PK, UK + NOT NULL internally declared as PK

to prevent the wastage of space to be more precise

The space required for declaring UK and PK is equal 

---

here i have not compared memory for uk and pk...

i have told about why we need to identify records uniquely either by uk or by pk.

Answer 1

yes Given answer is Wrong ..it should return 0 rows..

In database the NULL is Undefined or Unknown .. so one Null can never be equal to another NULL ..

So as there is no matching .. it will simply return 0 rows...Definately it will compare NULL with NULL ...

but as they are unknown we can say nothing ...as a result compiler will simply return 0 rows

Answer 2

comparing null with null gives you error in mySql.

Comments

NULL when compared to null gives Null in SQL.