Set conditions in a MySQL database
Posted by davidnewcomb on 01 Nov 2010 in MySQL
Ever come across the situation where you need to search for sets of values in a (MySQL) database? SQL along the lines of:
SELECT col1, col2 FROM table1 WHERE (col3 = 5 and col4 = 6) or (col3 = 10 and col4 = 12) or (col3 = 15 and col4 = 3)I come across it all the time and it occurred to me that I am searching for a set of data and all of SQL is based on sets and subsets of data, so why can’t I search for a set in the same way as I search for a column? and you can! Here’s an example of how to do it. First up we’ll need some test data. I’m using MySQL but it’s standard SQL and should work on anything. So we’ll create a database, table an add some data:
CREATE DATABASE bigsoft_set_test; USE bigsoft_set_test; CREATE TABLE tab( a VARCHAR(1), b VARCHAR(1) ); INSERT INTO tab(a, b) VALUES("a", “b"); INSERT INTO tab(a, b) VALUES("b", “c"); INSERT INTO tab(a, b) VALUES("c", “d"); INSERT INTO tab(a, b) VALUES("d", “e");Ok, lets start with an example of the old method:
SELECT a, b FROM tab WHERE (a = “b” and b = “c") or (a = “c” and b = “d");This gives us the middle 2 rows from our table:
+------+------+ | a | b | +------+------+ | b | c | | c | d | +------+------+ 2 rows in set (0.00 sec)Now let’s try it using sets:
SELECT a, b FROM tab WHERE (a, b) in (("b", “c"), ("c", “d"));Surrounding a comma separated list of values with braces creates a set. Our set could equally be a sub-query for example:
SELECT a, b FROM tab WHERE (a, b) in (SELECT * FROM tab WHERE a = “b” OR a = “c");The star “*” in the query means all columns which on this table is “a” and “b". The sub query returns a narrowed list of values to the outer query which is just passed upwards. Similarly we can create a 3 step set query as follows:
SELECT a, b FROM tab WHERE (a, b) in (SELECT * FROM tab WHERE a in ("b", “c"));But why would you want this? Well there could be a couple of reasons:
- The main one is that of verboseness. Having to define the fields in a AND/OR/braces takes up a lot of space on your query line which is limited.
- Parse speed. The smaller the query, the less time it takes to parse, and yes it does make more difference than you think. Pre-parsing slowness is the reason we have the prepareStatement commands in all the connectors.
- Code simplicity. I deal with ranges of values and by dealing with them as sets, not caring about there individual components I have simplified my code base significantly.
No feedback yet
Form is loading...