Create an SQL statement to return the required Information, and only the required Information
SQL WHERE Clause
The second way you control the results set is by limiting the rows that are returned in response to your query by using the WHERE clause. Suppose, for example, that you want to return the first name of the individual who has a last name of Andrews. Here is an example SQL statement that would accomplish this:
SELECT firstname FROM CUSTOMER
WHERE lastname='Andrews'
The result would be a single column, Firstname. There is one catch: If you have more than one row in the database with a last name of Andrews, you will get back more than one first name. To get around this, you can combine elements, providing more than one test that needs to be passed to be part of the results set. Simply place the keyword AND between the tests. So, if there was more than one customer with the last name of Andrews, you could try:
SELECT firstname FROM CUSTOMER
WHERE lastname='Andrews' AND firstname='Julie'
Horizontal Partitioning
The jargon term for this is horizontal partitioning. This refers to the fact that you are making cuts on the information that pertains to rows that are returned. In this specific example, we have employed both horizontal and vertical partitioning techniques because we have indicated both a limited set of columns to return and a filter on the rows to be considered.
Horizontal and vertical partitioning in SQL don't directly filter the number of rows and columns returned in a query.
They serve different purposes:
Horizontal partitioning:
Divides a large table into smaller, manageable subsets based on data values (e.g., year, region).
Speeds up queries targeting specific data subsets by only accessing relevant partitions.
Doesn't filter rows within a partition based on query criteria.
Vertical partitioning:
Splits a table into separate tables, each containing a subset of columns.
Improves performance by reducing data size for frequently accessed columns.
Doesn't affect the number of rows returned by a query.
However, they can indirectly influence the efficiency of filtering through these strategies:
Horizontal partitioning:
By limiting the data scanned to relevant partitions, you naturally reduce the number of rows processed, improving query performance and filtering efficiency.
Vertical partitioning:
By reducing the amount of data transferred (fewer columns) for each row, queries can potentially scan data faster, again indirectly improving filtering efficiency.
Ultimately, while these partitioning techniques don't directly filter results, they can significantly improve the speed and efficiency of filtering queries by reducing the amount of data accessed. Here's an additional point to consider:
If a query's WHERE clause leverages the partitioning key used for horizontal partitioning, only relevant partitions will be accessed, effectively filtering rows before any further processing. So, while not directly filtering rows and columns, partitioning plays a crucial role in optimizing queries and ultimately achieving efficient filtering for large datasets.
Theory behind the WHERE clause
You can use the WHERE clause to filter unwanted rows from the result. This filtering capability gives the SELECT statement its real power. In a WHERE clause, you specify a search condition that has one or more conditions that need to be satisfied by the rows of a table. A condition or predicate, is a logical expression that evaluates to true, false, or unknown. Rows for which the condition is true are included in the result; rows for which the condition is false or unknown are excluded. (An unknown result, which arises from nulls, is described in the next section.) SQL provides operators that express different types of conditions. Operators are symbols or keywords that specify actions to perform on values or other elements.
Figure 6-3: A sample syntax diagram for a where clauses using the DELETE statement
Variable items in a SQL statement (such as the table name and search condition in Figure 6-3) are shown in lowercase italics.
It is up to you to specify the appropriate item value(s) each time the statement is used. Optional clauses and keywords, such as the WHERE clause are indicated by alternate paths through the syntax diagram. When a choice of optional keywords is offered, the default choice (that is, the behavior of the statement if no keyword is specified) is UNDERLINED.
