Fed up with long WHERE-clauses?

SQL is a very easy to use language and it is pretty easy to make things work out of the box. In fact, from my point of view simplicity is one of the core advantages of SQL in general. But, what if you want to compare dozens of columns with each other? It is not hard to do but it can be a fair amount of unnecessary typing. 

A lengthy example

Let us create a table featuring 4 columns:

test=# CREATE TABLE t_test (a int, b int, c int, d int);

CREATE TABLE

To do the test we can add a simple row:

test=# INSERT INTO t_test VALUES (1, 2, 3, 4);

INSERT 0 1

Now we want to see all

test=# SELECT * FROM t_test WHERE a = 1 AND b = 2 AND c = 3 AND d = 4;

 a | b | c | d

---+---+---+---

 1 | 2 | 3 | 4

(1 row)

This is simple, yet pretty lengthy. The point is: When you are about to write a query you want a result – not a typing exercise.

Simplified filtering

To make this entire filtering process a little easier, we can use brackets:

test=# SELECT * FROM t_test WHERE (a, b, c, d) = (1, 2, 3, 4);

 a | b | c | d

---+---+---+---

 1 | 2 | 3 | 4

(1 row)

From my point of view this is ways more readable and a lot faster to write.
But, there is one more to do this:

test=# SELECT * FROM t_test WHERE t_test = (1, 2, 3, 4);

 a | b | c | d

---+---+---+---

 1 | 2 | 3 | 4

(1 row)

Remember, in PostgreSQL every table can be seen as a composite type. So, this means that we can use the name of the table inside the WHERE clause directly. PostgreSQL will compare one field after the other and see if they are all equal. This is an even shorter version to compare those fields with each other.

Hans-Juergen Schoenig
Hans-Jürgen Schönig has 15 years of experience with PostgreSQL. He is consultant and CEO of the company „Cybertec Schönig & Schönig GmbH“ (www.postgresq-support.de), which has served countless customers around the globe.