Introduction to Select Statements
Select statements can be the most complicated of all SQL statements. It is important to note that select statements are the only statements that will operate on multiple tables at the same time. For this reason, select statements support a slightly different, although standard, SQL syntax. This will be explained further when we talk about joins between tables, but let's first examine a simple SELECT statement.
Basic Select Statements
SELECT CATEGORY, TITLE, REVENUE FROM MOVIE WHERE REVENUE > 10000000
SELECT is a keyword that specifies that the following will be a list of information to be returned by the search.
CATEGORY specifies that the MOVIE CATEGORY is the first column of the result.
TITLE specifies that the MOVIE TITLE is the second column of the result.
REVENUE specifies that the MOVIE REVENUE is the third column of the result.
FROM is a keyword that tells the database that the following will be a list of tables.
MOVIE specifies that these columns come from the MOVIE table.
WHERE is a keyword that specifies that the following will be a list of search conditions.
REVENUE > 10000000 specifies that we want all of the movies in the database that have a REVENUE of more than $10,000,000.
SELECT TITLE FROM MOVIE
Simple select statement that returns all the values in the column TITLE from the table MOVIE
SELECT CATEGORY, TITLE, REVENUE FROM MOVIE
This select statement returns all the values for the columns CATEGORY, TITLE, REVENUE, in the order that the statement is constructed, from the table MOVIE
SELECT * FROM MOVIE
This statement will return all the data from the MOVIE table. It is not recommended that the '*' be used in a production environment as you have little control over the order that the columns are returned. The reason is that the SELECT * puts the columns in the order that they physically appear in the database. If you are dependent on having the columns returned in a specific order then you should specify the columns in the select statement.
Sorting with “ORDER BY”
SELECT TITLE FROM MOVIE ORDER BY TITLE
Selects all the values in the column TITLE and orders the returned data on the TITLE column. You can also specify that the data is sorted on a column where the data is not being retrieved.
You can control the sort order of the returned data by using DESC (descending sort order) and ASC (ascending sort order) keywords. The ORDER clause will sort data in ascending order by default. So to sort the returned data in a descending order you would use:
SELECT TITLE, CATEGORY FROM MOVIE ORDER BY CATEGORY DESC
You can order on more than one column and still use the order clause. For example, the following will allow you to firstly order the data on the CATEGORY descending and then order by TITLE ascending.
SELECT TITLE, CATEGORY FROM MOVIE ORDER BY CATEGORY DESC, TITLE
Note: Remember that if you are joining tables you will need to specify a table name or abbreviation for each column listed, for example:
ORDER BY movie.TITLE
It is also important to note that the returned order of your data will be affected by the International Sort Order. This can be set via OpenBase Manager Preferences.
The FROM Clause
The FROM clause in reference to the SELECT statement specifies the tables that will be used in the search. Using the FROM clause, you can also specify table abbreviations to be used when joining tables. Following are some examples of FROM clauses: FROM MOVIE
In this case, all columns in the query are from the MOVIE table and no abbreviations are needed. However, the following example shows the definition of table abbreviations.
FROM MOVIE mov, STUDIO stu
In this example, two tables are specified and abbreviations are specified. These abbreviations should be used throughout the query to tell OpenBase which table each column belongs to. When joining tables you should specify and use table aliases.
Finding Data - WHERE clause
The WHERE clause can be added to your SQL to find specific information in your database. Constraining the search on the server is more efficient than filtering via the application on the client side. This section will offer information on how to use where clauses effectively to find data.
A simple WHERE clause in a SELECT statement would look like this
SELECT CATEGORY, TITLE, REVENUE FROM MOVIE WHERE CATEGORY = 'Action'
This would select all records where the CATEGORY column is equal to 'Action'.
WHERE Clause Operators
OpenBase supports the following WHERE clause operators:
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE TALENT_ID = 90
Returns 1 single row where the TALENT_ID is equal to 90
!= Non Equality
SELECT CATEGORY, TITLE, REVENUE FROM MOVIE WHERE CATEGORY != 'Action'
Returns all rows other than rows where the category is equal to 'Action'
< Less than
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE TALENT_ID < 90
Returns 3 rows where the TALENT_ID is less than 90
<= Less then or equal to
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE TALENT_ID <= 90
Returns 4 rows where the TALENT_ID is equal to or less than 90
> More than
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE TALENT_ID > 620
Returns 8 rows where the TALENT_ID is greater than 620
>= More than or equal to
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE TALENT_ID >= 620
Returns 9 rows where the TALENT_ID is greater than or equal to 620
A SoundsLike B
OpenBase supports a modified soundex function designated by the operator SoundsLike
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE FIRST_NAME SOUNDSLIKE ('Jim')
Returns 19 rows where the FIRST_NAME sounds like the string Jim
A BETWEEN X AND Y
OpenBase supports BETWEEN for effectively constraining search results to a range of record values. When using this with indexes it is more effective than using two clauses specifying < > because it is evaluated as one clause.
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE FIRST_NAME BETWEEN ‘Jim’ AND ‘Scott’
Returns rows where FIRST_NAME is greater than equal string ‘Jim’ and less than equal ‘Scott’.
A IN (B)
Includes a row if A is in the list of values denoted by B. In this case B may be a comma separated list of values or a subselect.
SELECT FIRST _NAME, LAST _NAME FROM TALENT WHERE TALENT _ID IN (87, 88, 90)
Returns 3 rows where the TALENT_ID is equal to 87, 88 or 90
A NOT IN (B)
Includes a record if A is NOT in the list of values denoted by B. In this case
B may be a comma separated list of values or a subselect.
SELECT FIRST _NAME, LAST _NAME FROM TALENT WHERE TALENT _ID NOT IN (87, 88, 90)
Returns 459 rows where the TALENT_ID is not equal to 87,88 or 90
Includes a record if B exists, where B is a correlated select statement which returns one or more records.
IS NULL Is a NULL VALUE
SELECT c1,c2,c3 FROM t1 WHERE c1 IS NULL
NULL VALUES DEFINITION
NULL means there is no value. Empty string for a varchar is a valid value of zero length. Other data types either exist and have a valid value within the range of the datatype, or they don't exist (are unspecified) and are therefore marked as NULL.
For instance, if you have an integer column in a database, you would never have a case where there is an empty value. The value is either NULL (unspecified) or the value is a valid number. Empty is always interpreted as 0. Dates and datetimes are no different. If the value is not NULL, then the date must always be a valid date. There is no such thing as a blank value for dates and datetimes, just like there is no such thing as a blank value for integer columns. Unspecified values are NULL, but if the value exists then it must be in the range of the datatype.
A LIKE B
Includes a record if A matches the wild card pattern B
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE FIRST_NAME LIKE '%anc%'
Returns 3 rows where FIRST_NAME contains the string anc
OpenBase supports the following wildcards for LIKE statements.
Multiple character wildcards: % and *
This can be used at any point in the search string. Here is an example:
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE FIRST_NAME LIKE '%anc%'
This returns rows where FIRST_NAME contains the string anc.
Single character wildcard: _
The _ character will match against any single character.
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE FIRST_NAME LIKE '_eter'
This select statement returns rows where FIRST_NAME starts with any character followed by the string 'eter'.
Single chararacter match: 
Brackets are used to specify a set of characters that would be acceptable for the match to return true. For example:
SELECT FIRST _NAME, LAST _NAME FROM TALENT WHERE FIRST _NAME LIKE '[FB]%'
Returns rows where FIRST_NAME begins with F or B followed by additional characters.
The NOT Keyword
You may include the keyword NOT before each of the previous expressions to negate it. For instance, if you don’t want A to equal B, you could write the expression:
NOT A = B
SELECT CATEGORY, TITLE, REVENUE FROM MOVIE WHERE NOT CATEGORY = 'Action'
This would select all rows that are not equal to 'Action'.
Advanced searching using AND & OR operators
Operators can be strung together to produce multifaceted sets of criteria by using the AND and OR keywords. Here is an example of two criteria, both of which must be true for each record in the result.
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE FIRST_NAME LIKE '%anc%' AND LAST_NAME >'H'
This returns just a single row where the FIRST_NAME contains the string 'anc' and the LAST_NAME begins with a letter greater than 'H'.
The following example demonstrates the OR operator. In this case either condition can be true for a row to be returned.
SELECT FIRST_NAME, LAST_NAME FROM TALENT WHERE FIRST_NAME LIKE '%anc%' OR LAST_NAME LIKE '%ers%'
This statement returns 10 row where either the FIRST_NAME contains the string 'anc' or the LAST_NAME contains the string 'ers'.
Order of Evaluation
Use Parentheses to group AND/OR clauses. By default, OR’d expressions are evaluated before AND’s. So if you want to express two groups of ANDed constraints then you need to do something like this:
SELECT CATEGORY, TITLE, REVENUE FROM MOVIE WHERE (CATEGORY = 'Action' AND TITLE LIKE 'L*') OR (CATEGORY = 'Drama' AND TITLE LIKE 'P*')
EXISTS and NOT EXISTS
The EXISTS and NOT EXISTS keywords allow you to easily perform queries which list rows that do not match with records in another table. For instance, perhaps you want to select records in the MOVIE table where correlated records do not exist in the STUDIO table. The following example will list all movies which do not have studios on file.
SELECT * FROM MOVIE mov WHERE NOT EXISTS (SELECT STUDIO_ID FROM STUDIO stu WHERE mov.STUDIO_ID = stu.STUDIO_ID)
In this example, rows are only returned when matching records are not found in the subselect.
While the WHERE clause can be used with SELECT, UPDATE, INSERT, and DELETE queries, the SELECT query is the only one that operates on multiple tables. However, all SQL operations support subqueries which can be used to narrow a search using multiple tables.
SELECT statements like the one shown above provide an easy way to retrieve specific sets of information from the database. This first example only retrieves information from a single table. However, it is often necessary to retrieve data from several related tables at the same time. To do this we use joins.
A join is necessary when you want to view two tables with related information at the same time.
Using the Movies database you can display each movie title along with the studio name from the STUDIO table. The relationship can be made using the STUDIO_ID columns in each of the tables because each MOVIE's STUDIO_ID value points to a corresponding STUDIO_ID value in the STUDIO table. This enables the server to match movies with their studios.
SELECT mov.TITLE, mov.CATEGORY, stu.NAME FROM MOVIE mov, STUDIO stu WHERE mov.STUDIO_ID = stu.STUDIO_ID
This example uses the table aliases mov and stu to tell the server which table the TITLE, CATEGORY, and STUDIO columns come from. Table aliases or table names should be used whenever performing joins.
Inner & Outer Joins
The example above uses an inner join to match records in the MOVIE table with records in the STUDIO table. Inner joins only return complete matches, so movies with no matching studio will be removed from the result. However, outer joins include records even when there is no match.
OpenBase supports left outer joins, meaning that records from tables specified first are included even when they cannot be matched with records from tables on the right. An outer join is specified by using the * operator in place of the = operator in the WHERE statement. Here is an example:
SELECT mov.TITLE, mov.CATEGORY, stu.NAME FROM MOVIE mov, STUDIO stu WHERE mov.STUDIO_ID * stu.STUDIO_ID
The order of the tables in the FROM clause determine the direction of the outer join. Following is a more advanced example of a join.
SELECT mov.TITLE, mov.CATEGORY, tal.FIRST_NAME, tal.LAST_NAME, rol.ROLE_NAME FROM MOVIE mov, TALENT tal, MOVIE_ROLE rol WHERE mov.MOVIE_ID = rol.MOVIE_ID AND rol.TALENT_ID = tal.TALENT_ID AND tal.FIRST_NAME SoundsLike 'Jon' ORDER BY mov.CATEGORY, mov.TITLE
This complex query joins three tables together and returns each movie role, who played the role and the movie the role was played in. The last constraint narrows the query even more by only returning rows where the actor’s firstname sounds like Jon. A line-by-line breakdown of how this query code works is provided below.
SELECT mov.TITLE, mov.CATEGORY, tal.FIRST_NAME,tal.LAST_NAME, rol.ROLE_NAME
The above code snippet specifies the columns of the result. Each column name is preceded by a table alias so the database will know which table each value comes from.
FROM MOVIE mov, TALENT tal, MOVIE_ROLE rol
This code specifies the tables and table aliases used in the search. The aliases tal and rol are optional as long as you specify the full table name in front of each column in the query.
WHERE mov.MOVIE_ID = rol.MOVIE_ID
The above code constraint joins the movies in the MOVIE table to roles located in the MOVIE_ROLE table.
AND rol.TALENT_ID = tal.TALENT_ID
This joins each movie role with a talent. Failing to include these constraints will have a multiplication effect on the results, so it is very important to define how the tables relate to one another.
AND tal.FIRST_NAME SoundsLike 'Jon'
This constraint narrows the search by only returning result sets where the actor’s FIRST_NAME sounds like Jon. OpenBase supports a modified soundex function designated by the operator ‘SoundsLike’. This is one of those OpenBase specific extensions to SQL that we mentioned earlier in this chapter.
ORDER BY mov.CATEGORY, mov.TITLE
Finally, the code above orders the result rows by CATEGORY and TITLE.
Aggregate Functions and GROUP BY
Aggregate functions and the optional GROUP BY clause provide ways to retrieve summary information about table content. If no GROUP BY clause is specified in the SQL statement, aggregate functions will return a single summary row for all information satisfying the WHERE constraint.
Otherwise, the information will be placed in groups based on a common set of values, and a summary line will be generated for each.
The aggregate functions provided in this release are as follows:
Aggregate Function Description
count(*) Returns the number of rows returned for each group.
sum(columnName) Returns the sum of the column columnName for each group.
avg(columnName) Returns the average of the column columnName for each group.
min(columnName) Returns the minimum value of column columnName for each group.
max(columnName) Returns the maximum value of column columnName for each group.
std(columnName) Returns the standard deviation for each group.
The GROUP BY clause allows you to group results with common values. It works similarly to the ORDER BY clause, in that columns are specified and separated by commas. Here is an example:
SELECT count(*) MOVIES, sum(mov.REVENUE), stu.NAME STUDIO_NAME, mov.CATEGORY FROM MOVIE mov, STUDIO stu WHERE mov.STUDIO_ID = stu.STUDIO_ID AND mov.RATED = 'R' R GROUP BY stu.NAME, mov.CATEGORY
In this example, only studios with a movie rating of R are returned. Results are then grouped by the studio name and movie category. Finally, each group is converted into a single row of summary information. This example also demonstrates how you can specify columns from the GROUP BY section in the select statement to give the result more meaning.
The GROUP BY clause should be specified after the WHERE clause and before the ORDER BY clause (if you use these optional clauses).
The HAVING clause provides a way to eliminate grouped sets of records based on the results of aggregate functions. It should always be used in conjunction with GROUP BY to specify which groups of records you want included in the final result set. . HAVING will only accept groups of records that satisfy the criteria.
Here is an example from the pubs database that includes all authors that live in states with other authors.
SELECT state, count(state) from authors group by state having count(state) >1
Sometimes you may need to require groups to satisfy multiple criteria. This next example lists all states, which have more than one author but less than 10.
SELECT state, count(state) from authors group by state having count(state) > 1 and count(state)<10
The HAVING clause can only be used with the <, <=, !=, =, >= and > operators. A static value must also be present on the right side of the HAVING constraint.
Subqueries are useful when you want to include values from another table in a search without creating a direct join to the table. This might be the case if you are spanning a relationship to a second table only to qualify records in the first table.
Subqueries are entered between parentheses and normally appear in the WHERE clause. However, they can also be used as a data source for any function parameter.
Example: In what movie did they have a character referred to as Luke Skywalker?
Here is a subquery that uses the IN operator to answer this question:
select TITLE from MOVIE where MOVIE_ID in (select t2.MOVIE_ID from MOVIE_ROLE t2 WHERE t2.ROLE_NAME = ‘Luke Skywalker’)
The above example is referred to as a non-correlated subquery because the select in the subquery is only done once for all records searched in the movie table. In other words, the subquery does not have a qualifier that refers back to the parent select statement. The subquery can stand on its own. However, this is not true for the next example.
Here is a statement that uses the EXISTS operator and a correlated subquery to answer this question:
select TITLE t1 from MOVIE t1 where EXISTS (select t2.MOVIE_ID from MOVIE_ROLE t2 where t2.ROLE_NAME = ‘Luke Skywalker’ and t1.MOVIE_ID = t2.MOVIE_ID)
This is a correlated subquery because of the t1.MOVIE_ID = t2.MOVIE_ID in the subquery. t1 is an alias for the MOVIE table in the parent select. For each record searched in the MOVIE table the subselect needs to be re evaluated to see if records exist. For this reason correlated subqueries are generally less efficient than non-correlated subqueries. However, they can be very useful in some circumstances.
Stored Procedure Subqueries
Now it’s possible to call stored procedures as subqueries, a capability that’s especially useful to query other databases.
select TITLE t1 from MOVIE t1 where t1.MOVIE_ID IN (call FindMoviesForRole(‘Luke Skywalker’) )
Calling the RemoteJDBCQuery() stored procedure as a subquery, for instance, allows a result from Sybase or Oracle to be used to evaluate a query in OpenBase. RemoteJDBCQuery() allows any JDBC compliant database to be used.
The use of procedures as subqueries is limited to selects and inserts. It cannot be used for updates or deletes.
It is also important to note that only non-correlated subqueries will work with stored procedures. Each procedure is called only once, so parameters have to be static. You cannot pass in column names as parameters and use it as a function.
Fetch Ranges and Limits
It is sometimes desirable to truncate search results—or return a range of records within a result. This is especially true when you have a result of 2 million records but your users only want to see the first few hundred. In these cases, it is sometimes more efficient to tell the server to return just what you want.
The RETURN RESULTS keywords can be appended to the end of your SQL select to return a range of records or truncate a result. The example below shows how to truncate a search result after the first 100 rows.
SELECT * FROM MOVIES RETURN RESULTS 100
A second example below returns the second hundred rows by specifying a range. In this case, 100 is the starting point and 200 is the stopping point.
SELECT * FROM MOVIES RETURN RESULTS 100 TO 200