Finding values using Where clause on corresponding columns ( MYSQL )

Question

I trying to query this sql statement

SELECT * 
FROM `tablename` 
WHERE value1 IN (column1) 
AND value2 IN (column2) 
AND value3 IN (column3)...

So in this query the WHERE clause is finding the value1 in column1 and value2 in column2 and so forth

this although works perfectly...

but my problem is this, is there a better way to write this code something similar like The Insert query statement below.

INSERT INTO table_name (column1, column2, column3, ...) 
                VALUES (value1, value2, value3, ...);

u can see that the values correspond with the columns

could a similar statement be written like this

 SELECT * 
    FROM `tablename` 
    WHERE  VALUES (value1, value2, value3, ...); 
    IN (column1 AND column2 AND column3 AND ...)

Please see the question guide, a Minimal, Reproducible Example might help add clarity. — Stu
– Stu, Commented Apr 2, 2022 at 10:56
I took sometime editing and explaining it better could u plz take some time out to look at it if it make better sense now ? — Suleman Mughal
– Suleman Mughal, Commented Apr 2, 2022 at 11:49

forpas · Accepted Answer · 2022-04-02 11:56:45Z

1

I don't know why you even thought of the operator IN.

Your query should be written like:

SELECT * 
FROM `tablename` 
WHERE column1 = value1
  AND column2 = value2
  AND column3 = value3 ...

Another way to apply the same logic is this:

SELECT * 
FROM `tablename` 
WHERE (column1, column2, column3, ...) = (value1, value2, value3, ...)

answered Apr 2, 2022 at 11:56

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Suleman Mughal Over a year ago

thanks that helped alot... one more question what if some of the values i am generating are empty strings is it still possible to run the query? SELECT * FROM tablename WHERE (column1, column2, column3, ...) = (value1, " ", value3, ...) somthing like this ?

forpas Over a year ago

@SulemanMughal an empty string is '' without any spaces. The query checks for exact equality. All columns must be equal to their corresponding values. It works for any non-null values. If there are nulls, you can use <=> instead of =.

Suleman Mughal Over a year ago

i have another question regarding the syntax of a sql command UPDATE table_name SET (column1, column2, column3)=(value1, value2, value3) WHERE id = value could this command run in mysql

forpas Over a year ago

@SulemanMughal no, this syntax is not supported by MySql.

forpas Over a year ago

@SulemanMughal no, only the standard syntax: UPDATE table_name SET column1=value1, column2=value2, column3=value3 WHERE id = value

|

Collectives™ on Stack Overflow

Finding values using Where clause on corresponding columns ( MYSQL )

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related