SQL WHERE

7 Key excerpts on "SQL WHERE"

• 

  eBook - PDF

  Oracle Data Warehouse Tuning for 10g

  • Gavin JT Powell(Author)
  • 2011(Publication Date)
  • Digital Press

    (Publisher)

  The negative effect of this is granularity, but this negative effect depends on how applications are coded. For instance, connecting to and disconnecting from a database for every SQL code statement is extremely inefficient. 7.1 Basic Query Tuning This section examines the following topic areas: SELECT clause columns WHERE clause filtering 164 7.1 Basic Query Tuning Aggregating Functions Conditions and Operators Pseudocolumns Joins 7.1.1 Columns in the SELECT Clause It is always faster to SELECT exact column names, as shown in Figure 7.1, as opposed to simply selecting all columns, because Oracle has to resolve columns in metadata before execution. For large tables, the difference is likely to be negligible, as shown in the following query: SELECT * FROM sale; If there is an index, then use it. Reading column values directly from an index without reading a table at all is faster, because the index occupies less physical space. There is, therefore, less I/O. In the example shown in Figure 7.2, only the index is read. 7.1.2 Filtering with the WHERE Clause The WHERE clause can be used to either include wanted rows or exclude unwanted rows, or both. The WHERE clause can be used to tune SQL statements simply by attempting to match WHERE clause specifications to indexes, sorted orders, and physical ordering in tables. In other words, filter according to how the metadata is constructed. Figure 7.1 Selecting the entire table. 7.1 Basic Query Tuning 165 Chapter 7 Note: The optimizer in the latest versions of Oracle Database is capable of using indexes when columns are not ordered, such that index column order no longer needs to precisely match WHERE clause column sequencing. However, good programming practice is best in the long run for application stability. Portability between different programmers is also important. Figure 7.3 shows a query with a WHERE clause not matching an index such that a full table scan is executed.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Introduction to Database and Knowledge-Base Systems

  • S Krishna(Author)
  • 1992(Publication Date)
  • WSPC

    (Publisher)

  SQL was the product of a major research and development project undertaken by IBM to implement a relational database system. The database management system to emerge out of this effort was known as System R. The commercial products which 4.1 SQL 69 followed are known as DB2 and SQL/DS. With its emergence as a stand-ard, SQL is available on practically all hardware and software platforms. Several DBMS packages (INGRES, dBASEIV etc.) whose principal inter-face is different, also provide an SQL interface. SQL incorporates all aspects of database creation, interaction and management. Our concern here will be mainly with the features for data retrieval or querying. The SQL term for a relation is a table. An attribute is a column (or a field) and a tuple is a row (or a record). A query as expressed in SQL can ask for values for one or more columns in one or more tables which satisfy a set of conditions. A query has basically a very simple syntax of the form SELECT .... FROM .... WHERE... The general form of the query with the optional clauses indicated by [...] is: SELECT [ALL/DISTINCT] columnname(s) FROM table(s) [WHERE predicate(s)][GROUP BY field(s)] [HAVING predicate] [ORDER BY column_name(s)] The following preliminary remarks concern the use of table names and column names in queries. 1. Range variables: A range variable may be associated with each table. Use of range variables is optional and may be dispensed with in most cases. When table names are long, use of range variables rather than full table names could shorten expression of search conditions. Range variables, however, are necessary when a table has to be present in more than one operand position. In Q8 below, for example, we need the join of a table with itself. This is the only example in which range variables are neces-sary, in the examples in this chapter. 2. Qualifying column names: Column names may be qualified with the name of the table (or range variable) to which they belong, together with a .

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Database Modeling Step by Step

  • Gavin Powell(Author)
  • 2020(Publication Date)
  • CRC Press

    (Publisher)

  What does all this mean without using a plethora of nasty long words? In short, SQL was developed as a shorthand method of retrieving information from relational databases, and SQL has become the industry standard over the last 20 years. Here is an example of a query (a question posed to the database that asks for specific information), which is written in SQL and retrieves all the rows in a table called AUTHOR:

  SELECT AUTHOR_ID,  NAME FROM AUTHOR;

  4.1.2    SQL for Different Databases

  SQL as implemented in different database vendor products is usually standardized using American National Standards Institute (ANSI) standardized SQL, but individual database vendors will have different naming conventions, different syntax, and even extra add-on bells and whistles. In reality, different database vendors develop a unique relational database and Relational Database Management Systems (RDBMS) containing a proprietary form of SQL.

  Relational Database Management System (RDBMS) is a term used to describe both a database and the associated tools (database management toolkit) used to work with and manage database software.

  So what are the basics of SQL?

  4.1.3    Introducing SQL

  This section summarizes the different types of SQL as described in the following list:

  •  Query Command. Querying or reading a database is performed with a single command called the SELECT command, which is used to retrieve data from tables in a database. There are various ways in which data can be retrieved from tables:

  ○  Basic Query. Retrieve all rows from a single table.

  ○  Filtered Query. A filtered query uses a WHERE clause to include or exclude specific rows.

  ○  Sorted Query. Sorting uses the ORDER BY clause to retrieve rows in a specific sorted order.

  ○  Aggregated Query

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Understanding Databases

  Concepts and Practice

  • Suzanne W. Dietrich(Author)
  • 2021(Publication Date)
  • Wiley

    (Publisher)

  NOTE 5.1 SQL Syntax Although SQL is a standard, each database product is an approximation to that standard. This text intro- duces the SQL standard where queries preceded by a checkmark ( √ ) in this chapter have been verified using the MySQL database. See the book’s companion website for supporting files. Students are strongly encouraged to run the examples while reading the text and to examine the results of the queries. 113 114 SQL: AN INTRODUCTION TO QUERYING The basic SQL query expression selects a list of attributes from relations where a condi- tion holds: select a1, a2, … , aj from r1, r2, … , rk where  This basic SQL query expression is essentially equivalent to the relational algebra expression:  a1, a2, …, aj (  (r1 × r2 × · · · × rk)) A basic SQL query consists of select, from, and where clauses, listing the desired attributes from the Cartesian product of the tables where the specified condition holds on the data. The select clause projects the desired attributes from the Cartesian product of the tables that sat- isfy the selection condition specified by the where clause. It is interesting to note that the SQL select clause is in fact a relational algebra projection and not a selection. Another point of interest to note is that the from clause represents a Cartesian product of the tables specified. One of the most common mistakes in SQL is the lack of realization that the from clause provides all possi- ble combinations of the tuples from the tables specified in the list. If a relational algebra join is desired, then the join condition is specified as part of the where clause, which performs a selection on the result of the Cartesian product. 1 There are slight variations on this SQL syntax. If all attributes are desired in the result, an asterisk (*) provides a convenient shorthand: select * from r1, r2, … , rk where  Use * to select all attributes.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Fundamentals of Database Management Systems

  • Mark L. Gillenson(Author)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  74 Chapter 4 Relational Data Retrieval: SQL The SQL SELECT statement can also be used to accomplish a relational Project operation. This is a vertical slice through a table involving all rows and some attributes. Since all of the rows are included in the Project operation, there is no need for a WHERE clause to limit which rows of the table are included. For example, ‘‘List the salesperson number and salesperson name of all of the salespersons.’’ SELECT SPNUM, SPNAME FROM SALESPERSON; results in: SPNUM SPNAME 137 Baker 186 Adams 204 Dickens 361 Carlyle To retrieve an entire table, that is to design an SQL SELECT statement that places no restrictions on either the rows or the attributes, you would issue: SELECT * FROM SALESPERSON; and have as the result: SPNUM SPNAME COMMPERCT YEARHIRE OFFNUM 137 Baker 10 1995 1284 186 Adams 15 2001 1253 204 Dickens 10 1998 1209 361 Carlyle 20 2001 1227 Comparisons In addition to equal (=), the standard comparison operators, greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and not equal to (<>) can be used in the WHERE clause. ‘‘List the salesperson numbers, salesperson names, and commission percentages of the salespersons whose commission percentage is less than 12.’’ SELECT SPNUM, SPNAME, COMMPERCT FROM SALESPERSON WHERE COMMPERCT<12; This results in: SPNUM SPNAME COMMPERCT 137 Baker 10 204 Dickens 10 Data Retrieval with the SQL SELECT Command 75 As another example: ‘‘List the customer numbers and headquarters cities of the customers that have a customer number of at least 1700.’’ SELECT CUSTNUM, HQCITY FROM CUSTOMER WHERE CUSTNUM>=1700; results in: CUSTNUM HQCITY 1700 Washington 1826 New York 2198 New York 2267 New York ANDs and ORs Frequently, there is a need to specify more than one limiting condition on a table’s rows in a query. Sometimes, for a row to be included in the result it must satisfy more than one condition.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Wiley Pathways Introduction to Database Management

  • Mark L. Gillenson, Paulraj Ponniah, Alex Kriegel, Boris M. Trukhnov, Allen G. Taylor, Gavin Powell, Frank Miller(Authors)
  • 2012(Publication Date)
  • Wiley

    (Publisher)

  The first thing is that each SQL command has a specific command syntax that describes how you run the command. The command syntax includes the command name and any com- mand keywords and parameters. Command keywords, also known as command clauses, are used to describe specific actions that a command will take. Com- mand parameters are values that you supply so the command can run. Take a look at the following example, a simplified version of the SELECT statement: SELECT select_list FROM table [WHERE qualifying_conditions] Let’s break this down. SELECT is the command name. The SELECT com- mand has various uses, but is most commonly used in queries to retrieve val- ues from tables and views. The select_list, when SELECT is used in this man- ner, is the list of columns that you want the command to return. The select_list is one of the SELECT command parameters. Command syntax descriptions typ- ically use italicized type, as in this example, to identify replaceable parameters. FROM is a command keyword defining the FROM clause. When using SELECT to retrieve data, FROM is a required keyword. It identifies the object or objects (typically tables and views) from which you are retrieving data. WHERE is an optional keyword and is used to filter the retrieval result. When retrieving data, if you don’t include a WHERE clause, all rows are returned. The WHERE clause lets you set qualifying conditions, also called the search argument, that limit the rows returned. For example, you might want to filter a numeric column by the value it contains, or a date/time column by a range of dates. Square brackets are typically used in command syntax descrip- tions when describing command syntax to identify optional clauses. In actual command strings, square brackets can be used to enclose object names. When you run any command, SQL Server parses the command before it does anything else.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Introductory Relational Database Design for Business, with Microsoft Access

  • Jonathan Eckstein, Bonnie R. Schultz(Authors)
  • 2017(Publication Date)
  • Wiley

    (Publisher)

  logical_expression following WHERE may be arbitrarily complicated. For example:

  SELECT ProductName FROM PRODUCT WHERE UnitsOnOrder*UnitPrice > 5000;

  shows the name of each product for which the total value of inventory on order is over $5,000. In addition to simple comparisons using = (equal), > (greater than), < (less than), <= (less than or equal to), and > = (greater than or equal to), SQL allows compound logical expressions constructed through AND and OR operations. For example:

  SELECT ProductName, UnitsOnOrder, UnitPrice FROM PRODUCT WHERE UnitsOnOrder >= 100 OR UnitPrice < 50;

  displays the name, number of units on order, and unit price of each product that has at least 100 units on order or has a price less than $50. Note that OR in SQL is “inclusive,” as in most computer languages, so that a record meeting both sub‐conditions is considered to satisfy the OR condition. In the query above, for example, a product with at least 100 units on order and also costing less than $50 would be displayed in the query output. Here is another example of a compound condition:

  SELECT FirstName, LastName FROM CUSTOMER WHERE City="Hamilton" AND State="NJ";

  This query will display the names of all customers from Hamilton, NJ; the database also contains a customer from Hamilton, NY, but this customer is not included in the query result because the State field value does not match the condition. When comparing text fields to literal character strings such as “CA”, you should enclose the literal character strings in double quotes. Otherwise, SQL will try to interpret the character string as an attribute name.

  Inner Joins

  So far, this chapter has considered only queries drawn from a single table. We now discuss how SQL can express queries based on data from multiple tables. The most common technique for basing queries on multiple tables is called an inner join. An inner join consists of all combinations of rows selected from two tables that meet some matching condition, formally called a join predicate

  Sign up to read

  Learn more about book