 |
VOOZH | about |
|
VOOZH | about |
We’re so glad you’re here. You can expect all the best TNS content to arrive Monday through Friday to keep you on top of the news and at the top of your game.
Check your inbox for a confirmation email where you can adjust your preferences and even join additional groups.
Follow TNS on your favorite social media networks.
Become a TNS follower on LinkedIn.
Check out the latest featured and trending stories while you wait for your first TNS newsletter.
SELECT statement, while SQL used for data manipulation uses the INSERT, UPDATE, DELETE and MERGE statements. The data manipulation statements are collectively called Data Manipulation Language or DML.
This article will break down the anatomy of SQL query language, while the second part of the series will describe DML.
SELECT and FROMSELECT without FROMJOINWHEREGROUP BYHAVINGORDER BYOFFSETFETCHOFFSET and FETCHSELECT statement includes a couple of elements, but only the first two are required: SELECT and FROM. However, some databases including Oracle Database and MySQL make the FROM clause optional if the SELECT refers only to self-contained expressions, such as SELECT 1;SELECT sysdate; and SELECT my_function();. In these cases, the data is not derived from a table, hence FROM is not required.
Optional components are illustrated by putting [ ] around them.
SELECT <expressions> FROM <table or sub-query> [ JOIN <to other table> ON <join condition> ] [ WHERE <predicates> ] [ GROUP BY <expressions> [ HAVING <predicates> ] ] [ ORDER BY <expressions> ] [ OFFSET ] [ FETCH ]
SELECT component is processed after the HAVING clause. The following lists the order that which the clauses are processed and their purpose:
FROM: Indicates which table(s) to retrieve data from. The FROM clause determines the working set of data that is being retrieved. This usually refers to a table but can also include a subquery (another SELECT query that acts as the input source for the current query).JOIN: Specifies the rules for joining multiple tables. The JOIN clause is part of the FROM clause and combines the data from multiple tables into one data set. It is one of the fundamental operators of the relational model to combine different relations into one set. The JOIN clause allows join conditions that ensure that only rows that logically belong together are joined (rows with matching primary key –> foreign key relationships). Multiple JOIN clauses can be specified to join multiple tables into the data set. Because the JOIN clause is part of the FROM clause, it cannot be specified without a preceding FROM statement in the query.WHERE: Filters the rows returned by the query. The WHERE clause filters the data set based on the provided predicates or filter conditions and discards all rows that do not match them. It narrows down the results, for example, to retrieve all countries from the continent of Europe instead of all countries in the world.GROUP BY: Aggregates (or groups) rows with common values in the specified column(s) into one row. The resulting row is sometimes referred to as a grouping set. Because rows with common values are aggregated into one row, there will only be as many rows as there are unique values. For values of columns not specified in GROUP BY, aggregate functions in the SELECT clause are required to aggregate these values per group.HAVING: Filters rows resulting from the GROUP BY clause. It is therefore a part of the GROUP BY and cannot be used without a preceding GROUP BY statement in the query.SELECT: Defines the list of column(s) and expression(s) to appear in the query result output. The SELECT clause computes any expressions and defines the list of columns to be returned, or projected, as the query result.ORDER BY: Identifies which column(s) to use to sort the resulting data and in which direction to sort them (ascending or descending). If the ORDER BY is omitted, the order of the rows returned by a SQL query is undefined.OFFSET: Specifies the number of rows to skip in the result set before returning the data.FETCH: Specifies the number of rows to return from the result.SELECT and a FROM clause:
SQL> SELECT * 2* FROM regions; REGION_ID NAME ____________ ________________ AF Africa AN Antarctica AS Asia EU Europe NA North America OC Oceania SA South America 7 rows selected.
* after SELECT, which is short for “all columns”) from a table called regions. If you want to return a given list of columns, you can call them out specifically:
SQL> SELECT name 2* FROM regions; NAME ________________ Africa Antarctica Asia Europe North America Oceania South America 7 rows selected.
SELECT statement can also compute expressions, for example, 1+2. Technically, neither the constant 1 nor the constant 2 come from any table, but the ISO SQL standard nevertheless requires a FROM clause. Many databases have “dummy” tables to enable such queries, such as the dual table in Oracle Database.
SQL> SELECT 1+2 2* FROM dual; 1+2 ______ 3
FROM clause in such cases:
SQL> SELECT 1+2; 1+2 ______ 3
regions table, and the new countries table. To write a query that joins both tables into one result, you use the JOIN clause. Without a JOIN clause, if you specify the two tables in the FROM clause, every row from the table regions will be multiplied by every row from the table countries. This is often referred to as the cross product and it’s a common mistake that SQL beginners make. For example:
SQL> SELECT r.name, c.name 2* FROM regions r, countries c; NAME NAME _________ ___________________________________ Africa Kosovo Africa Yemen Africa South Africa Africa Zambia Africa Zimbabwe Africa Andorra Africa United Arab Emirates Africa Afghanistan Africa Antigua and Barbuda Africa Albania Africa Armenia Africa Angola Africa Argentina Africa Austria Africa Australia ... ... ... South America Uzbekistan South America Vatican City South America Saint Vincent and the Grenadines South America Venezuela South America Vietnam South America Vanuatu South America Samoa 1,372 rows selected.
countries table with the regions table where the region_id is the same. This is generally referred to as the join condition and can be specified in the ON clause as part of the JOIN clause:
SQL> SELECT r.name, c.name 2 FROM regions r 3 JOIN countries c 4* ON (r.region_id=c.region_id); NAME NAME _________ ___________________________________ Africa South Africa Africa Zambia Africa Zimbabwe Africa Angola Africa Burkina Faso Africa Burundi Africa Benin ... ... ... South America Ecuador South America Guyana South America Peru South America Paraguay South America Suriname South America Uruguay South America Venezuela 196 rows selected.
SELECT r.name, c.name and put the letters r and c next to the table names. These are table aliases and are required to tell the database which table column you want. If the statement just said SELECT name, name, it would be unclear whether the query refers to the regions table column name or the countries table column name.
WHERE clause filters the rows produced by the FROM clause. Until now, you always got all the rows that were in the tables. If you want to return only all countries in South America, this is where the WHERE clause comes in. It can be used to match all rows with the regions.name column value 'South America':
SQL> SELECT r.name, c.name 2 FROM regions r 3 JOIN countries c 4 ON (r.region_id=c.region_id) 5* WHERE r.name = 'South America'; NAME NAME ________________ ____________ South America Argentina South America Bolivia South America Brazil South America Chile South America Colombia South America Ecuador South America Guyana South America Peru South America Paraguay South America Suriname South America Uruguay South America Venezuela 12 rows selected.
GROUP BY clause is often a puzzle for SQL beginners. The clause is used to aggregate multiple rows into a group, essentially combining multiple rows into one row. When would such a thing ever be useful? Well, for example, the countries table contains a column called population but the regions table does not:
SQL> SELECT r.name, c.name, c.population 2 FROM regions r 3 JOIN countries c 4 ON (r.region_id=c.region_id) 5* WHERE r.name = 'South America'; NAME NAME POPULATION ________________ ____________ _____________ South America Argentina 44694000 South America Bolivia 11306000 South America Brazil 208847000 South America Chile 17925000 South America Colombia 48169000 South America Ecuador 16291000 South America Guyana 741000 South America Peru 31331000 South America Paraguay 7026000 South America Suriname 598000 South America Uruguay 3369000 South America Venezuela 31689000 12 rows selected.
regions table does not have a column with that information, the answer can be provided only by calculating the sum of the population column for each country per region. So, you need a mechanism that takes the 196 rows of the countries table and puts them into seven groups or buckets based on their region (because there are seven rows in the regions table). However, the query cannot just put the 196 rows into seven rows; it needs to calculate the sum of the population per region based on the populations of the countries belonging to the region.
This can be done by applying the SUM() aggregate function over the population column:
SQL> SELECT r.name, SUM(c.population) 2 FROM regions r 3 JOIN countries c 4 ON (r.region_id=c.region_id) 5* GROUP BY r.name; NAME SUM(C.POPULATION) ________________ ____________________ Africa 1263685000 Asia 4439011000 Europe 748985000 North America 575767000 Oceania 37556000 South America 421986000 6 rows selected.
'Antarctica', but there is no country with that region_id in the countries table. Hence, the JOIN clause filters that region out (because there is no matching region_id in the countries table as specified by the ON clause).
A GROUP BY clause does not require any JOIN clause; you can create groups in just a single table. For example, “How many countries start with the same letter?” can also be answered via a GROUP BY. To do this, create as many groups per the unique first letter value for all rows by using the SUBSTR() function, and then count the rows that fall into that group or category:
SQL> SELECT SUBSTR(name,1,1), COUNT(*) 2 FROM countries 3* GROUP BY SUBSTR(name,1,1); SUBSTR(NAME,1,1) COUNT(*) ___________________ ___________ K 6 Y 1 S 26 Z 2 A 11 U 7 B 17 C 17 D 5 G 11 E 8 F 3 M 18 H 3 I 8 J 3 N 11 L 9 O 1 P 9 Q 1 R 3 T 12 V 4 24 rows selected.
HAVING clause filters rows resulting from the GROUP BY clause based on the predicate(s) provided. For example, if you want to return only the regions that have a population of more than 500 million people, this cannot be specified in the WHERE clause because the WHERE clause is processed before the GROUP BY clause. Hence, the WHERE clause has no notion of the population of a region. This is where the HAVING clause comes in. From a logical perspective, it behaves the same as the WHERE clause, but it filters at a different processing stage:
SQL> SELECT r.name, SUM(c.population) 2 FROM regions r 3 JOIN countries c 4 ON (r.region_id=c.region_id) 5 GROUP BY r.name 6* HAVING SUM(c.population) > (500 * 1000 * 1000); NAME SUM(C.POPULATION) ________________ ____________________ Africa 1263685000 Asia 4439011000 Europe 748985000 North America 575767000
ORDER BY clause sorts the resulting data. So far, the undefined sorting of the rows has worked out, except when it came to “countries per first letter.” The ORDER BY clause can be used to return the rows in alphabetical order:
SQL> SELECT SUBSTR(name,1,1), COUNT(*) 2 FROM countries 3 GROUP BY SUBSTR(name,1,1) 4* ORDER BY SUBSTR(name,1,1); SUBSTR(NAME,1,1) COUNT(*) ___________________ ___________ A 11 B 17 C 17 D 5 E 8 F 3 G 11 H 3 I 8 J 3 K 6 L 9 M 18 N 11 O 1 P 9 Q 1 R 3 S 26 T 12 U 7 V 4 Y 1 Z 2 24 rows selected.
DESC (DESCENDING) keyword:
SQL> SELECT SUBSTR(name,1,1), COUNT(*) 2 FROM countries 3 GROUP BY SUBSTR(name,1,1) 4* ORDER BY SUBSTR(name,1,1) DESC; SUBSTR(NAME,1,1) COUNT(*) ___________________ ___________ Z 2 Y 1 V 4 U 7 T 12 S 26 R 3 Q 1 P 9 O 1 N 11 M 18 L 9 K 6 J 3 I 8 H 3 G 11 F 3 E 8 D 5 C 17 B 17 A 11 24 rows selected.
OFFSET clause specifies the number of rows to skip before starting to return data. This clause is shorthand for what would otherwise require analytic queries or subqueries. For example, asking “Give me all countries in South America ranked by square kilometers except the first three” can be answered with:
SQL> SELECT c.name, c.area_sq_km 2 FROM countries c 3 JOIN regions r 4 ON (c.region_id=r.region_id) 5 WHERE r.name = 'South America' 6 ORDER BY area_sq_km DESC 7* OFFSET 3 ROWS; NAME AREA_SQ_KM ____________ _____________ Colombia 1138910 Bolivia 1098581 Venezuela 912050 Chile 756102 Paraguay 406752 Ecuador 283561 Guyana 214969 Uruguay 176215 Suriname 163820 9 rows selected.
FETCH clause specifies the number of rows to return from the result. Some databases call this the LIMIT clause. Like the OFFSET clause, this is also a shorthand and can be used to answer business questions like “What are the top three countries in terms of population?” This can be answered with:
SQL> SELECT name, population 2 FROM countries 3 ORDER BY population DESC 4* FETCH FIRST 3 ROWS ONLY; NAME POPULATION ________________ _____________ China 1384689000 India 1296834000 United States 329256000
FETCH clause provides the ONLY and WITH TIES keywords. The above just used ONLY because it is unlikely that two countries have the same population.
However, when ranking countries by letter, there is much more room for overlap. For example, in the countries per first letter example, when ranked by number of countries, it is clear that some letters have the same number:
SQL> SELECT SUBSTR(name,1,1), COUNT(*) 2 FROM countries 3 GROUP BY SUBSTR(name,1,1) 4* ORDER BY COUNT(*) DESC; SUBSTR(NAME,1,1) COUNT(*) ___________________ ___________ S 26 M 18 B 17 C 17 T 12 A 11 N 11 G 11 L 9 P 9 I 8 E 8 U 7 K 6 D 5 V 4 J 3 H 3 F 3 R 3 Z 2 Q 1 Y 1 O 1 24 rows selected.
FETCH clause on this query, the letter C would be omitted from the results, although it has exactly the same number of countries as the letter B:
SQL> SELECT SUBSTR(name,1,1), COUNT(*) 2 FROM countries 3 GROUP BY SUBSTR(name,1,1) 4 ORDER BY COUNT(*) DESC 5* FETCH FIRST 3 ROWS ONLY; SUBSTR(NAME,1,1) COUNT(*) ___________________ ___________ S 26 M 18 B 17
WITH TIES keyword comes in, as it will include ties in the results:
SQL> SELECT SUBSTR(name,1,1), COUNT(*) 2 FROM countries 3 GROUP BY SUBSTR(name,1,1) 4 ORDER BY COUNT(*) DESC 5* FETCH FIRST 3 ROWS WITH TIES; SUBSTR(NAME,1,1) COUNT(*) ___________________ ___________ S 26 M 18 B 17 C 17
OFFSET and FETCH clauses allow another nice shorthand that would otherwise require an analytical query or subquery. Consider the question “What is the second smallest country on the planet in terms of square kilometers?” This can be answered by combining OFFSET to return results starting from the second row, and FETCH to fetch just the second row:
SQL> SELECT name, area_sq_km 2 FROM countries 3 ORDER BY area_sq_km 4 OFFSET 1 ROW 5* FETCH FIRST 1 ROW ONLY; NAME AREA_SQ_KM _________ _____________ Monaco 2
