Product Documentation

Appearance

WHERE Clause ​

Use the WHERE clause for two distinct purposes: to filter rows based on some search criteria and to join tables based on one or more join conditions.

[ WHERE condition ]

Note the following rules:

  • The results of aggregate functions may not be constrained in the WHERE clause. See HAVING Clause.
  • Columns that do not exist in tables defined in the FROM clause may not be restricted in the WHERE clause.
  • Inner joins can be expressed in the FROM clause or the WHERE clause. Outer joins must be expressed in the FROM clause.
  • Equality conditions are supported for all join types. Inequality conditions are not supported for full outer joins.

For example, the following query joins four tables, and it filters rows based on additional search criteria. In this case, parentheses are used to separate the join conditions from the other constraints:

premdb=# SELECT season_name, matchday, h.name hname, a.name aname, ftscore
FROM season s, hometeam h, awayteam a, match m
WHERE (s.seasonid=m.seasonid AND h.htid=m.htid AND a.atid=m.atid) 
AND hname='Chelsea' AND season_name='2011-2012' 
ORDER BY matchday;
 season_name |      matchday       |  hname  |          aname          | ftscore 
-------------+---------------------+---------+-------------------------+---------
 2011-2012   | 2011-08-20 00:00:00 | Chelsea | West Bromwich Albion    | 2-1
 2011-2012   | 2011-08-27 00:00:00 | Chelsea | Norwich City            | 3-1
 2011-2012   | 2011-09-24 00:00:00 | Chelsea | Swansea City            | 4-1
 2011-2012   | 2011-10-15 00:00:00 | Chelsea | Everton                 | 3-1
 2011-2012   | 2011-10-29 00:00:00 | Chelsea | Arsenal                 | 3-5
 2011-2012   | 2011-11-20 00:00:00 | Chelsea | Liverpool               | 1-2
 2011-2012   | 2011-11-26 00:00:00 | Chelsea | Wolverhampton Wanderers | 3-0
 2011-2012   | 2011-12-12 00:00:00 | Chelsea | Manchester City         | 2-1
 2011-2012   | 2011-12-26 00:00:00 | Chelsea | Fulham                  | 1-1
 2011-2012   | 2011-12-31 00:00:00 | Chelsea | Aston Villa             | 1-3
 2011-2012   | 2012-01-14 00:00:00 | Chelsea | Sunderland              | 1-0
 2011-2012   | 2012-02-05 00:00:00 | Chelsea | Manchester United       | 3-3
 2011-2012   | 2012-02-25 00:00:00 | Chelsea | Bolton Wanderers        | 3-0
 2011-2012   | 2012-03-10 00:00:00 | Chelsea | Stoke City              | 1-0
 2011-2012   | 2012-03-24 00:00:00 | Chelsea | Tottenham Hotspur       | 0-0
 2011-2012   | 2012-04-07 00:00:00 | Chelsea | Wigan Athletic          | 2-1
 2011-2012   | 2012-04-29 00:00:00 | Chelsea | Queens Park Rangers     | 6-1
 2011-2012   | 2012-05-02 00:00:00 | Chelsea | Newcastle United        | 0-2
 2011-2012   | 2012-05-13 00:00:00 | Chelsea | Blackburn Rovers        | 2-1
(19 rows)

Parent topic:SELECT