SQL Hacks

Chapter 1. SQL Fundamentals

In this chapter, you will see some familiar SQL commands with some surprising variations. SQL includes many subtleties that the discerning programmer can exploit. With better SQL, you can do more processing at the database and less processing in your application. By and large, this redistribution of labor will be better for the application and better for the database; it should also reduce the traffic between these components. In addition, by improving your SQL, you will make your queries easier to read.

Each main SQL engine has a command-line interface. Although such interfaces appear ancient, they are still essential utilities for all SQL hackers. Each interface has its own peculiarities, but they all do essentially the same job. You can find details of the command-line interfaces for SQL Server, Oracle, MySQL, Access, DB2, and PostgreSQL in “Run SQL from the Command Line” [Hack #1].

Run SQL from the Command Line

The command-line processor is the lowest common denominator when it comes to running SQL, but you’ll find plenty of times when it comes in handy.

All of the popular SQL engines (except Access) have serviceable command prompt utilities that are installed by default, and all provide roughly the same benefits:

• You can type in SQL and see the results or error messages displayed immediately.

• You can start them up from an operating system prompt.

• You can specify the username and password that you want to use.

• You can pipe SQL statements in from another ...