Concepts/Basic/

The SELECT * EXCLUDE and SELECT * RENAME Clauses

An introduction to the SELECT * EXCLUDE and SELECT * RENAME clauses and their related keywords

SELECT * EXCLUDE

It is possible to use SELECT * and exclude one or more columns from the query results.

The query to see everything in the Intakes table EXCEPT the ids and the names would look like:

SELECT * EXCLUDE animal_id, name
  FROM austin_animal_center_intakes
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code

And the first seven rows of the result would look like this:

datetime monthyear found_location intake_type intake_condition animal_type sex_upon_intake age_upon_intake breed color
2017-12-07T14:07:00 2017-12 Colony Creek And Hunters Trace in Austin (TX) Stray Normal Dog Neutered Male 10 years Spinone Italiano Mix Yellow/White
2014-12-19T10:21:00 2014-12 8700 Research Blvd in Austin (TX) Public Assist Normal Dog Neutered Male 7 years Spinone Italiano Mix Yellow/White
2015-11-13T15:57:00 2015-11 1912 E William Cannon Rd in Austin (TX) Stray Normal Cat Intact Female 16 years Domestic Longhair Mix Black/White
2015-03-05T14:49:00 2015-03 Austin (TX) Public Assist Normal Dog Neutered Male 11 years Rottweiler Mix Black/Brown
2016-03-27T00:04:00 2016-03 3614 Bill Price in Travis (TX) Public Assist Normal Dog Spayed Female 7 years Labrador Retriever/German Shepherd Brown/Black
2014-02-17T17:10:00 2014-02 Travis (TX) Owner Surrender Normal Dog Intact Male 3 years Rottweiler Mix Black/Tan
2014-02-17T17:10:00 2014-02 Austin (TX) Public Assist Normal Dog Intact Male 3 years Rottweiler Mix Black/Tan

SELECT * RENAME

You can also rename one or more columns when using SELECT *.

The query to see everything in the Intakes table and rename two of the columns would look like this:

SELECT * RENAME sex_upon_intake AS sex, age_upon_intake AS age
  FROM austin_animal_center_intakes
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code

And the first seven rows of the result would look like this:

animal_id name datetime monthyear found_location intake_type intake_condition animal_type sex age breed color
A006100 Scamp 2017-12-07T14:07:00 2017-12 Colony Creek And Hunters Trace in Austin (TX) Stray Normal Dog Neutered Male 10 years Spinone Italiano Mix Yellow/White
A006100 Scamp 2014-12-19T10:21:00 2014-12 8700 Research Blvd in Austin (TX) Public Assist Normal Dog Neutered Male 7 years Spinone Italiano Mix Yellow/White
A191351 Bri-Bri 2015-11-13T15:57:00 2015-11 1912 E William Cannon Rd in Austin (TX) Stray Normal Cat Intact Female 16 years Domestic Longhair Mix Black/White
A322813 Tyson 2015-03-05T14:49:00 2015-03 Austin (TX) Public Assist Normal Dog Neutered Male 11 years Rottweiler Mix Black/Brown
A553074 Jo Jo 2016-03-27T00:04:00 2016-03 3614 Bill Price in Travis (TX) Public Assist Normal Dog Spayed Female 7 years Labrador Retriever/German Shepherd Brown/Black
A672744 Oso 2014-02-17T17:10:00 2014-02 Travis (TX) Owner Surrender Normal Dog Intact Male 3 years Rottweiler Mix Black/Tan
A672744 Oso 2014-02-17T17:10:00 2014-02 Austin (TX) Public Assist Normal Dog Intact Male 3 years Rottweiler Mix Black/Tan

SELECT * EXCLUDE and SELECT * RENAME can be used together in the same query, and when used together you must specify EXCLUDE before RENAME. If you use both, you cannot specify the same column in both EXCLUDE and RENAME.

An example of a combined query would look like this:

SELECT * EXCLUDE animal_id RENAME sex_upon_intake AS sex
FROM austin_animal_center_intakes
siyeh/austin-animal-center-statistics-oct-1-2013-to-dec-7-2017 Run query Copy code

And the first seven rows of the result would look like this:

name datetime monthyear found_location intake_type intake_condition animal_type sex age_upon_intake breed color
Scamp 2017-12-07T14:07:00 2017-12 Colony Creek And Hunters Trace in Austin (TX) Stray Normal Dog Neutered Male 10 years Spinone Italiano Mix Yellow/White
Scamp 2014-12-19T10:21:00 2014-12 8700 Research Blvd in Austin (TX) Public Assist Normal Dog Neutered Male 7 years Spinone Italiano Mix Yellow/White
Bri-Bri 2015-11-13T15:57:00 2015-11 1912 E William Cannon Rd in Austin (TX) Stray Normal Cat Intact Female 16 years Domestic Longhair Mix Black/White
Tyson 2015-03-05T14:49:00 2015-03 Austin (TX) Public Assist Normal Dog Neutered Male 11 years Rottweiler Mix Black/Brown
Jo Jo 2016-03-27T00:04:00 2016-03 3614 Bill Price in Travis (TX) Public Assist Normal Dog Spayed Female 7 years Labrador Retriever/German Shepherd Brown/Black
Oso 2014-02-17T17:10:00 2014-02 Travis (TX) Owner Surrender Normal Dog Intact Male 3 years Rottweiler Mix Black/Tan
Oso 2014-02-17T17:10:00 2014-02 Austin (TX) Public Assist Normal Dog Intact Male 3 years Rottweiler Mix Black/Tan