mysql and or query together

Question

How can i run mysql and or query together instant of separate query. e.g.:

And query:

select * form tablename where name='A' and password="A" and id='A';

Or query:

select * form tablename where name='A' or password="A" or id='A';

-These are 2 different query,can i make these query together?what is the syntax??

Answer 1

Well, you can just union them but, since one is a subset of the other, it's not strictly necessary:

select * from tablename
    where name = 'A' and password = 'A' and id = 'A'
union select * from tablename
    where name = 'A' or password = 'A' or id = 'A'

That will give you exactly the same results as if you had just run the second query on its own. That will make sense once you realise that every single row from the first query has a name equal to 'A', so it will match the first part of the where clause in the second query.

If you want duplicate rows for those returned in both queries, just use union all instead of union.

If you were using 'A' as just a placeholder and its values are different in the two queries, then you have two approaches. Use a construct like:

... where (name = 'A' and password = 'B' and id = 'C')
       or  name = 'D' or  password = 'E' or  id = 'F'

or use the union solution I gave above, something like:

select * from tablename
    where name = 'A' and password = 'B' and id = 'C'
union select * from tablename
    where name = 'D' or password = 'E' or id = 'F'

(use union all when you know there is no possibility of duplicates between the two queries, - it will save the DBMS the trouble of removing non-existent duplicates - that's not the case with these queries).

The union may give better performance on a DBMS that can hive off the two selects more easily to separate query engines (something that would be more difficult with a single query with a complex where clause). Of course, as will all optimisations, measure, don't guess.

Answer 2

Use parentheses to group the conditions?

SELECT * FROM table WHERE (X and Y or Z) AND (P and Q or F)

Answer 3

Use () for such type of conditions

select * form tablename
         where name='A' OR password="A" OR id='A' OR 
                    (name='A' AND password="A" AND id='A')

If you want to check for same string as A here then you will get same o/p using following query

select * form tablename
         where name='A' OR password="A" OR id='A'

Answer 4

Just combine the conditions with WHERE

SELECT * FROM tablename WHERE (name='A' AND password='A' AND id='A') OR name='A' OR password='A' OR id='A'

The parentheses ensure that the whole AND expressions "validates" only if ALL the containing conditions are true while the rest macthes the OR

Answer 5

It is not clear what you expect as the result, but my guess is you want a UNION:

SELECT 1 `query`, `name`, `password`, `id`
FROM `tablename` WHERE `name`='A' and `password`='A' and `id`='A'
UNION
SELECT 2 `query`, `name`, `password`, `id`
FROM `tablename` WHERE `name`='A' or `password`='A' or `id`='A'

Note that the first column query in result is required to separate results from the two queries because union of (X and Y) and (X or Y) is always (X or Y).