Simple SQL: Pt. 1 - Page 3

Getting Acquainted with DISTINCT

An excellent keyword to get acquainted with is "DISTINCT". If you have data that is duplicated, but you only want to see one instance of it, DISTINCT is just the ticket.

For example, you have a table named CUSTOMERS. It is loaded with the names, addresses, phone numbers, and company names of all your best customers. Your boss needs to know what companies have been buying from you. Frequently you have multiple buyers from the same company, perhaps different divisions, so running a standard SELECT statement like this:

SELECT FIRST_NAME, LAST_NAME, COMPANY
       FROM CUSTOMERS;

will return something like this:

As is obvious, you have two customers from IsoLunacy, Inc in your query results. If this were thousands of duplicates, it would be a terrible headache to create a list manually of companies you deal with. So, you fire off a DISTINCT query such as this:

SELECT DISTINCT COMPANY FROM CUSTOMERS;

Which returns:

SELECT statements are also adept at handling comparisons such as greater than and less than. Imagine the table "EMPLOYEES" with Names, Addresses, Start dates, vacation days accrued, and vacation days taken contained within it. The boss wants to know (for the Human Resources Intranet) whether anyone has taken more vacation days than they have accrued. Your SELECT might look like this:

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES
       WHERE VACATION_TAKEN > VACATION_ACCRUED;

The results might look like:

Good information for HR, and bad information for the employee names resulting from this query.