CODEWITHSUNDEEP
mysql
karma
karma

Reputation: 137

Difference between AND and OR in WHERE condition of a SELECT statement in Mysql Database?

I have a database table called employee, When retrieving the name of the employee like this 

SELECT * FROM employee WHERE Fname ='jack' AND Fname ='john';

This query returns an empty set. Tried Querying the database using the OR condition like this 

SELECT * FROM employee WHERE Fname ='jack' OR Fname = 'John';

The query returns all the fields of jack and john from the employee table. My understanding of AND operator is 1 and 1 is true, where Jack and John is in my table it should return the fields of jack and john from the employee table using the AND operator. Am I allowed to use AND condition on a single table, Have I misunderstood the concept. Please some good advice!

Upvotes: 2

Views: 2429

Answers (4)

Balz Guenat
Balz Guenat

Reputation: 1711

What you write in the WHERE clause is a condition. Writing Fname = 'jack' AND Fname = 'john' looks for people whose first name is "jack" and at the same time "john". But what you want is people whose first name is "jack" or "john".

Upvotes: 2

Greg Hewgill
Greg Hewgill

Reputation: 993223

The condition Fname ='jack' AND Fname ='john' means the query will match each row where Fname is both 'jack' and 'john'. Since a single column can't be two different values at once, this will match no rows.

Upvotes: 4

Rohit Gupta
Rohit Gupta

Reputation: 4193

What you want is or because Fname can not be both jack and john

SELECT * FROM employee WHERE Fname ='jack' or Fname ='john';

Upvotes: 1

geoB
geoB

Reputation: 4704

WHERE Fname ='jack' AND Fname ='john' returns an empty set because there is no record where Fname is both 'jack' AND 'john'. 'jack <> 'john'. If you want to return records when the Fname can be either jack or john then use ...WHERE Fname ='jack' OR Fname ='john'.

Upvotes: 2

Related Questions