DB2 SQL - Some secrets

I don't know how many people know about this, but I found a neat trick when using DB2 SQL.
Usually you have to query something like this:

	select *
	from customer
	where last_name = 'SMITH'
	and age = 32

view raw db2_secret1.sql hosted with ❤ by GitHub

Now in DB2 SQL (at least it works in iSeries/AS400), you can simplify it like this:

	select *
	from customer
	where (last_name, age) = ('SMITH', 32)

view raw db2_secret2.sql hosted with ❤ by GitHub

Maybe it doesn't seem very useful, but most of the tables that I work with have a double or triple key (one being product and the other a serial number per product).
The real advantage comes when using a group of rows. Instead of using a temporary table and joining in the query, or making something horrible like:

	select *
	from customer
	where (last_name = 'SMITH' and age = 32)
	or (last_name = 'JONHSON' and age = 45)

view raw db2_secret3.sql hosted with ❤ by GitHub

It's much more simple to write it like this:

	select *
	from customer
	where (last_name, age) in (values ('SMITH', 32), ('JOHNSON', 45))

view raw db2_secret4.sql hosted with ❤ by GitHub