Non-unique rows in SQL

I always forget how to do this for no apparent reason:

SELECT row1, row2, count(*) AS instances FROM table GROUP BY row1, row2 HAVING count(*) > 1;

The sad part is that I do it on a pretty frequent basis…

SQL Order of Operations

I know this is rather easy to find, but writing it down helps me remember it:

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

This is extremely useful to keep in mind when you are writing queries.  It can help you keep things a bit more optimized and it is nice to know where things actually start and end.  It is also very useful to view how sql breaks down your query and goes about processing it.

Along these same lines it is useful to know what happens to a query once you hit enter.  Here is what it does in PostgreSQL:

1. Parsing – PostgreSQL looks at your query and makes sure it is valid before it even starts looking at the data.
2. Rules and Rewrite – Postgres uses rules to rewrite your query.  This is straight from the PostgreSQL docs – I couldn’t come up with a better way to say it.  The rewrite system takes the query tree created by the parser stage and looks for any rules (stored in the system catalogs) to apply to the query tree. It performs the transformations given in the rule bodies.One application of the rewrite system is in the realization of views. Whenever a query against a view (i.e. a virtual table) is made, the rewrite system rewrites the user’s query to a query that accesses the base tables given in the view definition instead.
3. Planning and Optimization – Postgres looks at the query it rewrote for you and goes about figuring out the best way to execute and does it the fastest way it can figure out.  Again from the docs: After the cheapest path is determined, a full-fledged plan tree is built to pass to the executor.
4. Executer – Finally your code gets executed and returned recursively to you. From the docs: This is essentially a demand-pull pipeline mechanism. Each time a plan node is called, it must deliver one more row, or report that it is done delivering rows.

I am sure I will be updating with more DB things soon.  There is much more detail here on postgres internals.

sql joins

I have been working on sql much more than I care to lately and I ran across a problem. I had a table that had 2 columns referencing the same table and I needed to do a join on each column seperately.

The solution:

select t1.id1, t2a.cell, t1.id2, t2b.cell
from table1 as t1
inner join table2 as t2a on t1.id1 = t2a.id
inner join table2 as t2b on t1.id2 = t2b.id;

Fairly simple after all…