*Selection in SQL*

On May 07, 2014, In The Database Language SQL by Admin

Views (1611)

The selection operator of relational algebra, and much more, is available through the WHERE clause of SQL. The expressions that may follow WHERE contain conditional expressions like those found in common languages such as C or Java.

We may build expressions by comparing values using the six common comparison operators: =, <>, <, >, <=, and >=. These operators have the same meanings as in C, but <> is the SQL symbol for "not equal to"; it corresponds to != in C.

The values that may be compared contain constants and attributes of the relations mentioned after FROM. We may also apply the usual arithmetic operators, +, *, and so on, to numeric values before we compare them. For example, (year - 1930) * (year - 1930) < 100 is true for those years within 9 of 1930. We may apply the concatenation operator | | to strings; for example foo | | 'bar' has value foobar.

An example comparison is

studioName = 'Disney'

in "The Database Language SQL" Example 1. The attribute studioName of the relation Movie is tested for equality against the constant 'Disney'. This constant is string-valued: strings in SQL are denoted by surrounding them with single quotes. Numeric constants, integers and reals, are also allowed, and SQL uses the common notations for reals such as -12.34 or 1.23E45.

The result of a comparison is a boolean value: either TRUE or FALSE. Boolean values may be combined by the logical operators AND, OR, and NOT, with their expected meanings. For example, we saw in "The Database Language SQL" Example 1 how two conditions could be combined by AND. The WHERE clause of this example evaluates to true if and only if both comparisons are satisfied; that is, the studio name is 'Disney' and the year is 1990. Here are some more examples of queries with complex WHERE clauses.

Example 1: The following query asks for all the movies made after 1970 that are in black-and-white.

SELECT title

FROM Movie

WHERE year > 1970 AND NOT inColor;

In this condition, we again have the AND of two booleans. The first is an ordinary comparison, but the second is the attribute inColor, negated. The use of this attribute by itself makes sense, because inColor is of type boolean. Next, consider the query

SELECT title

FROM Movie

WHERE (year > 1970 OR length < 90) AND studioName = 'MGM';

This query asks for the titles of movies made by MGM Studios that either were made after 1970 or were less than 90 minutes long. Notice that comparisons can be grouped using parentheses. The parentheses are required here because the precedence of logical operators in SQL is the same as in most other languages: AND takes precedence over OR, and NOT takes precedence over both.

### Tags

- relational algebra
- attributes
- concatenation operator
- Instead-Of Triggers
- Schema-Level Constraints and Triggers
- Tuple-Based CHECK Constraints
- Constraints on Attributes and Tuples
- Declaring Foreign-Key Constraints
- Keys Declared With UNIQUE
- Constraints and Triggers
- Interpreting Queries Involving Views
- Modifying Views
- View Definitions
- Introduction to Selection of Indexes
- Default Values / Indexes
- Simple Table Declarations
- Defining a Relation Schema in SQL
- Deletion / Updates
- Database Modifications
- Grouping / HAVING Clauses
- Full-Relation Operations
- Natural Joins / Outerjoins
- Subqueries in FROM Clauses
- Conditions Involving Tuples
- Subqueries
- Union, Intersection, and Difference of Queries
- Interpreting Multirelation Queries
- Disambiguating Attributes
- Queries Involving More Than One Relation
- Null Values and Comparisons Involving NULL
- Projection in SQL
- The Database Language SQL
- Additional Constraint Examples
- Referential Integrity Constraints
- Constraints on Relations
- Extending the Projection Operator
- Extended Operators of Relational Algebra
- Selection on Bags / Product of Bags / Joins of Bags
- Union, Intersection, and Difference of Bags
- Relational Operations on Bags
- A Linear Notation for Algebraic Expressions
- Dependent and Independent Operations
- Renaming
- Combining Operations to Form Queries
- Selection / Cartesian Product
- Set Operations on Relations
- An Algebra of Relational Operations
- Relational Algebra
- Attribute Lists
- Semistructured Data Representation
- Object-Oriented Versus Object-Relational
- Nested Relations
- What If There Is No Key
- Representing ODL Relationships
- Representing Other Type Constructors
- Representing Set-Valued Attributes
- Nonatomic Attributes in Classes
- Declaring Keys in ODL
- Subclasses in ODL / Multiple Inheritance in ODL
- Types in ODL
- Methods in ODL
- Multiplicity of Relationships
- Relationships in ODL / Inverse Relationships
- Attributes in ODL
- Introduction to ODL
- The Type System
- Decomposition into Fourth Normal Form
- Reasoning About Multivalued Dependencies
- Definition of Multivalued Dependencies
- Multivalued Dependencies
- Third Normal Form
- Boyce-Codd Normal Form
- Projecting Functional Dependencies
- Closing Sets of Functional Dependencies
- The Transitive Rule
- Why the Closure Algorithm Works
- Computing the Closure of Attributes
- Trivial Functional Dependencies
- The Splitting/Combining Rule
- Rules About Functional Dependencies
- Keys of Relations
- Using Null Values to Combine Relations - Comparison of Approaches
- An Object-Oriented Approach
- Converting Subclass Structures to Relations
- Handling Weak Entity Sets
- Combining Relations
- From E/R Relationships to Relations
- From Entity Sets to Relations
- Relation Instances
- Equivalent Representations of a Relation
- Tuples / Domains
- Attributes / Schemas
- The Relational Data Model
- Summary of The Entity-Relationship Data Model
- Weak Entity Set Notation
- Requirements for Weak Entity Sets
- Representing Keys in the E/R Model
- Keys in the E/R Model
- The Modeling of Constraints
- Picking the Right Kind of Element
- Design Principles
- Subclasses in the E/R Model
- Attributes on Relationships
- Elements of the E/R Model
- Database Programming
- The Query Processor
- Relational Database Systems