SQL Query Shortcuts

What are some cool SQL shorthands that you know of?

For example, something I learned today is you can specify to group by an index:

SELECT col1开发者_Go百科, col2 FROM table GROUP BY 2

This will group by col2

See Aaron Bertrand's "Bad Habits in SQL to kick" series - he has a post on this and highly recommends not using that notation:

• Bad habits to kick : ORDER BY ordinal

Ordinals - using numbers that are a reference to the place in the SELECT clause - can be used in the GROUP BY and ORDER BY clauses. But I don't recommend them because if the order changes, there'll be impact to your query.

Table aliasing is a must. IE:

SELECT
  FROM THIS_IS_MY_UNGODLY_TABLE_NAME timutn

...means I can use timutn rather than type the entire table name when I want/need to be clear which table a column comes from. It's essential when you have to join a table to itself, and who really wants to type the entire table name all the time anyway?

What really got me into table aliasing was that some SQL IDEs have smart enough intellisense to provide only a list of the columns for that table if you use the table alias.

If your conditions for your query aren't known when you actually run it, WHERE 1=1 is useful

eg

$query = 'SELECT * FROM table ';

<?php if ( $something ) { ?>
$sql.= 'WHERE something = ' . $variable;
<?php } else { ?>
$sql.= 'WHERE something = ' . $another;
<?php } ?>

If there's lots of branching going on it can be such a pain ( and no I don't believe in building query strings either but this is just for the sake of the example ).

Easier way:

$query = 'SELECT * FROM table WHERE 1=1 ';

<?php if ( $something ) { ?>
$sql.= 'AND something = ' . $variable;
<?php } else { ?>
$sql.= 'AND something = ' . $another;
<?php } ?>

Of course I would probably use a wrapper/class that has a where method in which I can specify conditions.