Retrieving Rows with SELECT - Goiit.com

shrubberystatuesqueΔιαχείριση Δεδομένων

1 Δεκ 2012 (πριν από 4 χρόνια και 8 μήνες)

211 εμφανίσεις

Retrieving Rows with SELECT

The heart of all SQL queries is the
SELECT

command.
SELECT

is used to build queries (also
known as
SELECT

statements). Queries are the only SQL instructions by which your data can be
retrieved from tables and views. The data returned via a query is called a
result set

and consists
of rows, with columns, similar to a table.

The columns of a result set are not
stored on the disk in any fixed form. They are purely a
temporary result of the query's requested data. A query on a table may return a result set with the
same column structure as the table, or it may differ drastically. Result sets may even have
columns
which are drawn from several other tables by a single query.

Since it is central to PostgreSQL,
SELECT

is easily the most complicated single command, having
the most available clauses and parameters. The following is the syntax for
SELECT
. The terms
used a
re summarized and described in greater detail within the following sections. The term
expression

is used to refer to either a column name, or a general expression (such as a column
being operated upon by a constant, or another column).


SELECT [ ALL | DIS
TINCT [ ON (
expression

[, ...] ) ] ]


target

[ AS
name

] [, ...]


[ FROM
source

[, ...] ]


[ [ NATURAL ]
join_type

source


[ ON
condition

| USING (
column_list

) ] ]


[, ...]


[ WHERE
condition

]


[ GROUP BY
expression

[, ...] ]


[ HAVING
condition

[, ...] ]


[ { UNION | INTERSECT | EXCEPT } [ ALL ]
sub
-
query

]


[ ORDER BY
expression


[ ASC | DESC | USING
operator

]


[, ...
] ]


[ FOR UPDATE [ OF
table

[, ...] ] ]


[ LIMIT {
count

| ALL } [ { OFFSET | , }
start

] ]

In this syntax diagram,
source

may be either a table name or a subselect. The syntax for these
general forms is as follows:


FROM { [ ONLY ]
table

[ [ AS ]
alias

[ (
column_alias

[, ...] ) ] ] |


(
query

) [ AS ]
alias

[ (
column_alias

[, ...] ) ] }

ALL


The
ALL

keyword may be specified as a noise term to make it clear that all rows should
be returned.

DISTINCT [ ON (
expression

[, ...] ) ]


The
DISTINCT

clause specifies a column (or expression) for which to retrieve only one
row per unique value of
expression
.

target

[ AS
name

] [, ...]


The
SELECT

targets are usually column names, though they can be constants, identifier,
function or gener
al expression. Each
target

requested must be separated by commas,
and may be named dynamically to
name

via the
AS

clause. Supplying the asterisk symbol
(
*
) as a target is shorthand for requesting all non
-
system columns, and may be listed
along with other t
argets.

FROM
source

[, ...]


The
FROM

clause dictates the
source

that PostgreSQL will look in for the specified
targets
. The
source
, in this case, may be a table name or a sub
-
query. You can specify
numerous sources, separated by commas. (This is roughly equivalent to a cross join). The
syntax for the
FROM

clause is described in more detail later in this section.

[ NATURAL ]
join_type

source

[ ON
condition

| USING (
column_list

) ]


The
FROM

sources may be joined together via the
JOIN

clause, which requires a
join_type

(e.g.,
INNER
,
FULL OUTER
,
CROSS
) and may require a
condition

or
column_list

to further define the nature of the join,
depending on the
join_type
.

WHERE
condition


The
WHERE

clause constrains the result set from the
SELECT

statement to specified criteria,
which are defined by
condition
. Conditions must return a single Boolean value (true or
false), but may consist of sever
al checks combined with logical operators (e.g., with
AND
,
and
OR
) to indicate that available rows must meet all supplied conditions to be included in
the statement's results.

GROUP BY
expression

[, ...]


The
GROUP BY

clause aggregates (groups) rows togeth
er by the criteria described in
expression
. This can be as simple as a column name (and often is) or an arbitrary
expression applied to values of the result set.

HAVING
condition

[, ...]


The
HAVING

clause is similar to the
WHERE

clause, but checks its con
ditions on aggregated
(grouped) sets instead of atomic rows.

{ UNION | INTERSECT | EXCEPT } [ ALL ]
sub
-
query


Performs one of three
set operations

between the
SELECT

statement and a second query,
returning their result sets in uniform column structure (which must be compatible).

UNION


Returns the set of collected rows.

INTERSECT


Returns the set of rows where the values of the two sets overlap.

EXCEPT


Returns the set of rows which are found in the
SELECT

statement, but not found in the
secondary query.

ORDER BY
expression


Sorts the results of the
SELECT

statement by
expression
.

[ ASC | DESC | USING operator ]


Determines whether or not the
ORDER BY

ex
pression

proceeds in ascending order (
ASC
),
or descending order (
DESC
). An
operator

may alternatively be specified with the
USING

keyword (e.g.,
<

or
>
).

FOR UPDATE [ OF
table

[, ...] ]


Allows for exclusive locking of the returned rows. When used within
a transaction block,
FOR UPDATE

locks the rows of the specified table until the transaction is committed.
While locked, the rows cannot be updated by other transactions.

LIMIT {
count

| ALL }


Limits the number of rows returned to a maximum of
count
, or ex
plicitly allows
ALL

rows.

{ OFFSET | , }
start


Instructs the
LIMIT

clause at what point to begin limiting the results. For example, a
LIMIT

with a
count

set to 100, and an
OFFSET

clause with a
start

value of 50 would
return the rows from 50 to 150 (if the
re are that many results to return).

Terms used in the
FROM

clause's syntax description are as follows:

[ ONLY ]
table


The
table

name specifies what table to use as a source for the
SELECT

statement.
Specifying the
ONLY

clause causes the rows of any child's table to be omitted from the
query.

[ AS ]
alias


An
alias

may optionally be assigned to a
FROM

source, in order to simplify a query (e.g.,
books

might be temporarily referenced with an alias of
b
). The
AS

term is con
sidered
noise, and is optional.

(
query

) [ AS ]
alias


Any valid
SELECT

statement may be placed in parentheses as the
query
. This causes the
result set created by the query to be used as a
FROM

source, as if it had been a static table.
This use of a sub
-
q
uery requires a specified
alias
.

(
column_alias

[, ...] )


The
FROM

sources which have assigned aliases may also alias columns by specifying
arbitrary column aliases. Each
column_alias

must be separated by commas, and
grouped within parentheses following the
FROM

source's alias. These aliases must match
the order of the defined columns in the table to which it is applied.

A Simple SELECT

A
SELECT

statement may be as simple as a request
for all rows and all columns from a specified
table. Use the following syntax to retrieve all rows and columns from a table:


SELECT * FROM
table_name
;

The asterisk (
*
) character, as mentioned in the explanation of
SELECT
's syntax, is short
-
hand for
all non
-
system columns. In essence, the
SELECT *

requests all non
-
system data in the table
named
table_name

; this retrieves all columns and all rows, because no row limit is specified.
To demonstrate,
Example 4
-
23

requests all columns (
*
) from Book Town's
books

table.

Example 4
-
23. Selecting all from the books table

booktown=#
SELECT * FROM books;


id | title |
author_id | subject_id

-------
+
-----------------------------
+
-----------
+
------------


7808 | The Shining | 4156 | 9


4513 | Dune | 1866 | 15


4267 | 2001: A Space Odyssey | 20
01 | 15


1608 | The Cat in the Hat | 1809 | 2


1590 | Bartholomew and the Oobleck | 1809 | 2


25908 | Franklin in the Dark | 15990 | 2


1501 | Goodnight Moon | 2031 |

2


190 | Little Women | 16 | 6


1234 | The Velveteen Rabbit | 25041 | 3


2038 | Dynamic Anatomy | 1644 | 0


156 | The Tell
-
Tale Heart | 115 | 9


41472 | Practical PostgreSQL | 1212 | 4


41473 | Programming Python | 7805 | 4


41477 | Learning Python | 7805 | 4


41478 | Perl Cookbook | 7806 | 4

(15 rows
)

Specifying Target Columns

While
SELECT *

is a good example of a basic query, and is sometimes very useful, you will
probably be interested in retrieving only a few columns worth of information at a time. To stay
efficient, and to keep your queries clear,

it is a good idea to explicitly specify the intended target
columns rather than to use the asterisk. This is especially true when using the
JOIN

clause, as
will be discussed in
the Secti
on called
Joining Data Sets with JOIN
."

To specify the target columns for a query, list the names of the columns following the
SELECT

keyword. The query will return data for only those columns that you list. The order of these
columns need not match their literal order in the table, and columns may be listed more than
once, or not at all, as shown in
Example 4
-
24
.

Example 4
-
24. Re
-
Ordering columns

booktown=#
SELECT id, author_id, title, id

booktown
-
#

FROM books;


id | author_id | title | id

-------
+
-----------
+
---------------
--------------
+
-------


7808 | 4156 | The Shining | 7808


4513 | 1866 | Dune | 4513


4267 | 2001 | 2001: A Space Odyssey | 4267


1608 | 1809 | The Cat in the Hat | 1608


159
0 | 1809 | Bartholomew and the Oobleck | 1590


25908 | 15990 | Franklin in the Dark | 25908


1501 | 2031 | Goodnight Moon | 1501


190 | 16 | Little Women | 190


1234 | 25041 | The Velveteen Rabbit | 1234


2038 | 1644 | Dynamic Anatomy | 2038


156 | 115 | The Tell
-
Tale Heart | 156


41472 | 1212 | Practical PostgreSQL | 41472


41473 | 7805 | Program
ming Python | 41473


41477 | 7805 | Learning Python | 41477


41478 | 7806 | Perl Cookbook | 41478

(15 rows)

As you can see, the data sets returned in both
Example 4
-
24

and
Example 4
-
23

are nearly
identical. The second set is returned in a different column arrangement, (omitting the
subject_id

column,

and repeating the
id

column twice) as a result of the target list.

Expressions, Constants, and Aliases

In addition to plain column names, targets in the
SELECT

statement may be arbitrary expressions
(e.g., involving functions, or operators acting upon identifiers), or constants. The syntax is
simple, and only requires that each identifier, expression, or constant be separated by commas.
Conveniently, different t
ypes of targets may be arbitrarily mixed in the target list.

In fact, the
SELECT

command may be used to retrieve expressions and constants without the use
of a
FROM

clause or specified columns, as in
Example 4
-
25
.

Example 4
-
25. Using expressions and constants

testdb=#
SELECT 2 + 2,

testdb
-
#

pi(),

testdb
-
#

'PostgreSQL is more than a calculator!';


?column? | pi | ?column?

----
------
+
------------------
+
---------------------------------------


4 | 3.14159265358979 | PostgreSQL is more than a calculator!

(1 row)

The target list allows the use of an optional
AS

clause for each specified target, which re
-
names a
column in the returned result set to an arbitrary name specified in the clause. The rules and
limitations for the specified name are the same as for normal identifiers (e.g., they may be quoted
to contain

spaces, may not be keywords unless quoted, and so on).

Using
AS

has no lasting effect on the column itself, but only on the result set which is returned by
the query.
AS

can be particularly useful when selecting expressions or constants, rather than plain

columns. Naming result set columns with
AS

can clarify the meaning of an otherwise ambiguous
expression or constant. This technique is demonstrated in
Example 4
-
26
, whi
ch shows the same
results as
Example 4
-
25
, but with different column headings.

Example 4
-
26. Using the AS clause with expressions and constants

booktown=#
SELECT 2
+ 2 AS "2 plus 2",

booktown
-
#

pi() AS "the pi function",

booktown
-
#

'PostgreSQL is more than a calculator!' AS comments;


2 plus 2 | the pi function | comments

----------
+
------------------
+
-------------------------------------
--


4 | 3.14159265358979 | PostgreSQL is more than a calculator!

(1 row)

Selecting Sources with the FROM Clause

The
FROM

clause allows you to choose either a table or a result set as a source for your specified
target list. Multiple sources may be entered following the
FROM

clause, separated by commas.
Specifying multiple sources in this fashion is functionally similar to a

CROSS JOIN
, discussed in
the Section called
Joining Data Sets with JOIN
."

Take care when specifying multiple
FROM

sources to PostgreSQL. The result of performing a
SELECT

on several comm
a
-
delimited sources without a
WHERE

or
JOIN

clause to qualify the
relationship between the sources is that the complete
Cartesian product

of the sources will be
returned. This is a result set where each column from each source is combined in every possible

combination of rows between each other source.

Typically a
WHERE

clause is used to define the relationship between comma
-
delimited
FROM

sources, as shown in
E
xample 4
-
27

(see
the Section called
Qualifying with the WHERE Clause
"
for more information about the
WHERE

clause).

You must be careful when identifying column names and using multiple sources in the
FROM

clause, as it can introduce ambiguity between identifiers. Consider a
SELECT

that draws from
both the
books

table and the
authors

table. Each of these tables has a col
umn called
id
. If
specified, PostgreSQL will be unable to determine if the
id

column refers to the book, or the
author:

booktown=#
SELECT id FROM books, authors;

ERROR: Column reference "id" is ambiguous

As a result of the potential for ambiguity, "comple
te" column names can be referenced through a
special syntax called
dot
-
notation
. Dot
-
notation refers to the placement of a dot, or period,
between the table name and a column name, in order to explicitly reference a particular column.
For example,
books.id

refers to the
id

column within the
books

table.

Dot
-
notation is only
required

in instances of ambiguity between data sets. As shown in
Example
4
-
27
, you can u
se the column name as an identifier source, as long as it is unique among the
available sets defined by the
FROM

clause. (In this case, the
title

column, which is unique to the
books

table, and the
last_name

column, which is unique to the
authors

tables).

Example 4
-
27. Selecting from multiple table sources

booktown=#
SELECT books.id, title, authors.id, last_name

booktown
-
#

FROM books, authors

booktown
-
#

WHERE books.author_id = authors.id;


id | title | id | last_na
me

-------
+
-----------------------------
+
-------
+
--------------


190 | Little Women | 16 | Alcott


156 | The Tell
-
Tale Heart | 115 | Poe


41472 | Practical PostgreSQL | 1212 | Worsley


2038 | Dynamic Anatomy

| 1644 | Hogarth


1608 | The Cat in the Hat | 1809 | Geisel


1590 | Bartholomew and the Oobleck | 1809 | Geisel


4513 | Dune | 1866 | Herbert


4267 | 2001: A Space Odyssey | 2001 | Clarke


1501 | Goodnig
ht Moon | 2031 | Brown


7808 | The Shining | 4156 | King


41473 | Programming Python | 7805 | Lutz


41477 | Learning Python | 7805 | Lutz


41478 | Perl Cookbook | 7806 | Christiansen


25
908 | Franklin in the Dark | 15990 | Bourgeois


1234 | The Velveteen Rabbit | 25041 | Bianco

(15 rows)

If you wish to use a sub
-
query to generate a result set as a source for your
FROM

clause, the entire
query must be surrounded by parenthes
es. This instructs PostgreSQL to correctly interpret the
query as a sub
-
SELECT

statement and to execute it before the
SELECT

statement within which it
resides.

Example
4
-
28

demonstrates a peculiar query which retrieves all column values (
*
) from the
books

table via a sub
-
query. The query then retrieves a string constant of
test

and the
id

values
from that result set (derived from the sub
-
query).

Example 4
-
28. Selecting f
rom a sub
-
query

booktown=#
SELECT 'test' AS test, id

booktown
-
#

FROM (SELECT * FROM books)

booktown
-
#

AS example_sub_query;


test | id

------
+
-------


test | 7808


test | 4513


test | 4267


test | 1608


test | 1590


test | 25908


test

| 1501


test | 190


test | 1234


test | 2038


test | 156


test | 41472


test | 41473


test | 41477


test | 41478

(15 rows)

The query in
Example 4
-
28

is rather peculiar because the net effect is no different than if you had
selected from the
books

table. This occurs because the result set from the sub
-
query is identical
to the set of values in the
books

table. The use of this query demonstrates the com
bination of a
string constant from one
SELECT

statement with a value drawn from the result set of a second
SELECT

statement. See
the Section called
Using Sub
-
Queries
" for more realistic examples of
sub
-
queries once you have a better understanding of the
SELECT

statement itself.

Note:
When specifying a table that is inherited by other tables, you may provide the optional
ONLY

keyword before the table name to indicate that you do not want to draw from
any sub
-
tables. (See
Chapter 7

for more information on inheritance.)

Aliasing FROM Sources

Like columns,
FROM

sources (e.g., tables, or sub
-
queries) may be aliased with the
AS

clause. This
is usually applied as a convenient shorthand for the dot
-
notation described in the preceding
section. Aliasing a data set allows you to refer to it via dot
-
notation, which provides a more
succinct and readable SQL statement.
Example 4
-
29

demonstrates the same query used in
Example 4
-
27
, however you can see tha
t it simplifies the dot
-
notation with the
AS

clause.

Example 4
-
29. Aliasing FROM sources

booktown=#
SELECT b.id, title, a.id, last_name

booktown
-
#

FROM books AS b, authors AS a

booktown
-
#

WHERE b.author_id = a.id;


id

| title | id | last_name

-------
+
-----------------------------
+
-------
+
--------------


190 | Little Women | 16 | Alcott


156 | The Tell
-
Tale Heart | 115 | Poe


41472 | Practical PostgreSQL

| 1212 | Worsley


2038 | Dynamic Anatomy | 1644 | Hogarth


1608 | The Cat in the Hat | 1809 | Geisel


1590 | Bartholomew and the Oobleck | 1809 | Geisel


4513 | Dune | 1866 | Herbert


4267 | 2001: A Sp
ace Odyssey | 2001 | Clarke


1501 | Goodnight Moon | 2031 | Brown


7808 | The Shining | 4156 | King


41473 | Programming Python | 7805 | Lutz


41477 | Learning Python | 7805 | Lutz


41478 | Perl Cookbook | 7806 | Christiansen


25908 | Franklin in the Dark | 15990 | Bourgeois


1234 | The Velveteen Rabbit | 25041 | Bianco

(15 rows)

In addition to placing aliases on the
FROM

clause's data sources, you can p
lace aliases on the
columns

within that source. This is done by following a valid data source's alias with a list of
column aliases
, grouped in parentheses and separated by commas. A column alias list therefore
consists of a sequence of identifier aliases
for each column, which correspond to the literal
columns in the order that the table is defined with (from left to right).

When describing a column alias list, you do not need to specify each column; any column that is
left unspecified is accessible via it
s normal name within such a query. If the only column you
wish to alias is to the right of any other columns that you do not necessarily wish to alias, you
will need to explicitly list the preceding columns (it is valid to list the same name for an existin
g
column as its "alias"). Otherwise, PostgreSQL will have no way of knowing which column you
were attempting to alias and will assume you were addressing the first column from the left.

Note:
The
AS

keyword is technically considered noise, and may be omitt
ed in practice;
PostgreSQL determines that any stray identifiers following a
FROM

source may be used as aliases.

Example 4
-
30

illustrates the same query that is used in
Example 4
-
29

but aliases the
id

columns
in each table to unique identifiers in order to reference them directly (i.e., without dot
-
notation).
The syntax is functionally ident
ical, aliasing only the
books

table's
id

column, thus making the
authors

table's
id

column non
-
ambiguous:

Example 4
-
30. Aliasing columns

booktown=#
SELECT the_book_id, title, id, last_name

booktown
-
#

FROM books AS b (the_book_id), authors

booktown
-
#

WHERE author_id = id;


the_book_id | title | id | last_name

-------------
+
-----------------------------
+
-------
+
--------------


190 | Little Women | 16 | Alcott


156 | The Tell
-
Tale Heart

| 115 | Poe


41472 | Practical PostgreSQL | 1212 | Worsley


2038 | Dynamic Anatomy | 1644 | Hogarth


1608 | The Cat in the Hat | 1809 | Geisel


1590 | Bartholomew and the Oobleck | 1809
| Geisel


4513 | Dune | 1866 | Herbert


4267 | 2001: A Space Odyssey | 2001 | Clarke


1501 | Goodnight Moon | 2031 | Brown


7808 | The Shining | 4156 | King


41473 | Programming Python | 7805 | Lutz


41477 | Learning Python | 7805 | Lutz


41478 | Perl Cookbook | 7806 | Christiansen


25908 | Franklin in the Dark | 15990 | Bourgeois


123
4 | The Velveteen Rabbit | 25041 | Bianco

(15 rows)

Removing Duplicate Rows with DISTINCT

The optional
DISTINCT

keyword excludes duplicate rows from the result set. If supplied without
the
ON

clause, a query that specifies
DISTINCT

will exclude any
row whose target columns have
already been retrieved identically. Only columns in the
SELECT
's target list will be evaluated.

For example, the
books

table has 15 rows, each with an
author_id
. Some authors may have
several entries in the
books

table, causin
g there to be several rows with the same
author_id
.
Supplying the
DISTINCT

clause, as shown in the first query in
Example 4
-
31
, ensures that the
result set will not have two
identical rows.

Example 4
-
31. Using DISTINCT

booktown=#
SELECT DISTINCT author_id

booktown
-
#

FROM books;


author_id

-----------


16


115


1212


1644


1809


1866


2001


2031


4156


7805


7806


15990


25041

(13 rows)


booktown=#
SELECT DISTINCT ON (author_id)

booktown
-
#

author_id, title

booktown
-
#

FROM books;


author_id | title

-----------
+
-----------------------


16 | Little Women


115 | The Tell
-
Tale
Heart


1212 | Practical PostgreSQL


1644 | Dynamic Anatomy


1809 | The Cat in the Hat


1866 | Dune


2001 | 2001: A Space Odyssey


2031 | Goodnight Moon


4156 | The Shining


7805 | Programming Python


7806 | Perl Cookbook


15990 | Franklin in the Dark


25041 | The Velveteen Rabbit

(13 rows)

As you can see, the first query in
Example 4
-
31

returns only 13 rows from
the
books

table, even
though there are 15 total rows within it. Two authors with two books each end up being
displayed only once.

The second query in
Example 4
-
31

uses a different

form of
DISTINCT
, which specifies the
columns (or expressions) to be checked for redundancies. In this case, 13 rows are still returned,
as the
ON

clause specifies to use the
author_id

column as the basis for determining if a row is
redundant or not. With
out the
ON

clause, the second query would return all 15 rows, because the
DISTINCT

clause would cause PostgreSQL to look for rows that are completely unique.

The titles that are omitted from the resultant data set by
ON

are arbitrarily determined by
PostgreSQL, unless an
ORDER BY

clause is specified. If the
ORDER BY

clause is used with
DISTINCT
, you can specify the order in which columns are selected; hence, you can select which
rows will be considered distinct first. See
the Section called
Sorting Rows with ORDER BY
" for
information about sorting rows.

If you are interested in grouping rows which have non
-
unique criteria, rather than omitting all
rows but one, see the de
scription of the
GROUP BY

clause in
the Section called
Grouping Rows
with GROUP BY
."

Qualifying with the WHERE Clause

The
WHERE

clause allows you to provide Boolean (tru
e or false) conditions that rows must satisfy
to be included in the resulting row set. In practice, a
SELECT

statement will almost always
contain at least one qualification via the
WHERE

clause.

For example, suppose that you want to see all of the books in

Book Town's Computers section.
The
subject_id

for the Computers subject is 4. Therefore, the
WHERE

clause can be applied with
an equivalence operation (the
=

operator) to check for all books in the
books

table with a
subject_id

equal to 4. This is demonstrated in
Example 4
-
32
.

Example 4
-
32. A simple WHERE clause

booktown=#
SELECT * FROM books

booktown
-
#

WHERE subject_id = 4;


id |

title | author_id | subject_id

-------
+
----------------------
+
-----------
+
------------


41472 | Practical PostgreSQL | 1212 | 4


41473 | Programming Python | 7805 | 4


41477 | Learning Python | 7805 |

4


41478 | Perl Cookbook | 7806 | 4

(4 rows)

The query in
Example 4
-
32

returns only rows whose
subject_id

column matches the integer
constant value of 4. Thus, only the four rows for computer books are returned, rather than the 15
rows shown by the simple query in
Example 4
-
2
3
.

The
WHERE

clause accepts numerous conditions, provided that they are joined by valid logical
keywords (e.g., the
AND
, and
OR

keywords) and returns a single Boolean condition. For example,
you may be interested in seeing all Book Town titles that fall un
der the Computers subject which
are
also

by the author Mark Lutz, thus joining two conditions to narrow the focus of your query.
Alternatively, you might be interested in seeing each of Book Town's titles that fall under either
the Computers subject or the Arts subject, thereby joining two condi
tions to broaden the focus of
your intended result set.
Example 4
-
33

demonstrates each of these scenarios using the
AND

keyword and
OR

keyword, respectively.

E
xample 4
-
33. Combining conditions in the WHERE clause

booktown=#
SELECT title FROM books

booktown
-
#

WHERE subject_id = 4

booktown
-
#

AND author_id = 7805;


title

--------------------


Programming Python


Learning Python

(2 ro
ws)


booktown=#
SELECT title FROM books

booktown
-
#

WHERE subject_id = 4

booktown
-
#

OR subject_id = 0;


title

----------------------


Dynamic Anatomy


Practical PostgreSQL


Programming Python


Learning Python


Perl Cookbook

(5 rows)

The first
SELECT

statement in
Example 4
-
33

combines one condition, which checks for titles in
the Computers subject (with a
subject_id

of 4), with another condition, which checks if the
author is Mark Lutz (with an
author_id

of 7805) via the
AND

keyword. The result is a smaller
data set, constrained to two rows that fit
both

specified conditions.

The second
SELECT

statement in
Example 4
-
33

combines the same first condition (books in the
Computers subject) with a second condition: if the title falls under the Arts subject (with a
subject_id

of
0). The result is a slightly larger data set of five rows that matched
at least one

of
these conditions.

WHERE

conditions may be grouped together indefinitely, though after two conditions you may
wish to group the conditions with parentheses. Doing so expl
icitly indicates how the conditions
are interrelated. As a demonstration, the two statements in
Example 4
-
34

have different effects
based merely on the ad
dition of parentheses.

Example 4
-
34. Grouping WHERE conditions with parentheses

booktown=#
SELECT * FROM books

booktown
-
#

WHERE author_id = 1866

booktown
-
#

AND subject_id = 15

booktown
-
#

OR subject_id = 3;


id |

title | author_id | subject_id

------
+
----------------------
+
-----------
+
------------


4513 | Dune | 1866 | 15


1234 | The Velveteen Rabbit | 25041 | 3

(2 rows)


booktown=#
SELECT * FROM books

bookt
own
-
#

WHERE author_id = 1866

booktown
-
#

AND (subject_id = 15

booktown(#

OR subject_id = 3);


id | title | author_id | subject_id

------
+
-------
+
-----------
+
------------


4513 | Dune | 1866 | 15

(1 row)

The preceding example demonstrates two attempts to look up Book Town titles with an
author_id

of 1866. The titles also have a
subject_id

of either 15, or 3. As you can see from
the first statement, when the three conditions are used without parentheses, th
e intent of the
statement is ambiguous, and interpreted incorrectly. The addition of parentheses will cause the
evaluations within parentheses to be considered before any surrounding condition.