Self Doubt on SQL AND operator

Question

Here why does the 5th query  select * from employees natural join works_on where PID = 'X' AND PID='Y'; is not working

The queries are 

The output are

Comments

PID is part of key attribute

right?

Then how it should have unique value

right?

---

because PID = 'X' and PID = 'Y' is not possible at the same time.. 'and' is a boolean operator which is checking the values of PID column..

---

@ankitgupta.1729

because it is part of key

right?

---

mam, it is not related to key.. "where PID = 'X' and PID = 'Y'" means if both PID ='X' and PID='Y' are true at the same time then it will give output otherwise not but both can't be true at the same time here. either PID='X' or PID='Y' will be true at a time or none.

---

@ankitgupta.1729

Check the 3rd and 4th table

for 1 and A , PID=X and PID=Y both true.

na? 

---

and is applied between two columns and not between the same column of different value i.e if pid1=x and pid2=y then it will work where pid1 and pid2 are both different columns , while for "or" it is evaluating true as it checking one condition at a time.

So ankitgupta is right.

Please tell me ankitgupta and srestha if I am wrong.

---

@sresthamam, it will check each every cell of PID column one by one whether both X and Y are there or not in that cell.. 

@kd.....yes,  correct but it will be worked also when same column name repeats like  when you write PID='X' and PID='X' and PID='X' ; and it will return rows where PID='X'.. right? 

---

@ankitgupta.1729

check this point too, and check 2nd table.

There 1 is not the key , but 1,X is the key, because 1 is not unique, but 1,X is unique.

Another point think about division operation

If there all attributes matches, then only returns. otherwise not return

So, it is possible to have 2 values for the query , then returns true.(maynot be in same row definitely)

right?

---

not getting mam... question is not related to key.. suppose instead of condition PID='X' and PID ='Y', If condition is PID='X' and NAME='A' then what should be output acc.  to u and how it comes?

---

@ankitgupta.1729

I havenot got , what ur query is.

Plz. see this example

PID=X, say X means employee who wear white shirt.

PID=Y, say Y means employee who wear red tie

Those who wear both can enter in Company.

Can this not a possibility?