From the course: Advanced SQL: Logical Query Processing, Part 1

Single data source queries - SQL Tutorial

From the course: Advanced SQL: Logical Query Processing, Part 1

Start my 1-month free trial

Single data source queries

- [Instructor] Our journey begins with the first step of every SQL query, establishing the source data set, the only data that will be used by all subsequent processing phases. So welcome to the FROM Clause. The FROM Clause results in the single data set in the form of a virtual table constructed from one or more sources, and then handed over to the following processing phase. Some databases, including SQL Server, PostgreSQL, MySQL and others, also support a SELECT without a FROM clause. You can think of a FROM-less SELECT as if it was using a hypothetical FROM clause that consists of a Dummy table containing one row, one column with some arbitrary value. Other databases force us to include an explicit FROM clause, even when it's not really used except as syntactic sugar. SAP HANA decided to literally call this table Dummy. Oracle decided to call it DUAL, and Informix sysdual, but the name doesn't really matter. They all serve the same purpose, to provide our query with a valid source data set to process. What is important is query processing order. First, the FROM clause is evaluated. Only then the resulting data set is passed on to the next processing phase. For this trivial query, it goes straight to the SELECT clause. At this point, the FROM clause is forgotten and no longer used. The SELECT clause evaluates each expression for each row of the data set that was handed over to it. Our dummy set had only one row so the resulting set will consist of one row, a single column expression containing our string, and no alias. The FROM clause accepts any source data set that fulfills the following requirements. Source data sets must be uniquely aliased as the query must be able to reference each set unambiguously. Each column within each source data set must uniquely aliased for the same reason, and the source data sets must not be ordered, and we will cover the reasons for that later. Side note, there is no requirement in SQL for neither the source data nor the results to consist of unique rows. This is one of the many aspects of SQL that doesn't agree with a relational model. SQL is a composable language. It allows the FROM clause to use data sets from any source as long as it complies with these rules. We can use a table, a view, a function, or a subquery derived table. The processing of a FROM with just one source is straightforward. The entire source data set is evaluated. The result is then gift wrapped as a virtual table and delivered to the next phase for further processing. This fundamental process will be the same whether our source is a single table or a 500 rows long nightmare with 20-level deep nested tables, views, functions, and subqueries. SELECT * FROM Staff returns all nine staff members. Following execution order, the query begins by evaluating the entire Staff table, passing it on to the SELECT, which will in turn translates the star to all column names and evaluates each one for every row. Now, I want you to try and guess the result of the following query. SELECT 'SQL IS FUN' FROM Staff. Side note, throughout this course, I will ask you to pause and answer puzzles and short questions. So whenever you see this icon, it's time to pause and think. Let's execute the query, and does it make sense? If it doesn't, let me add back the source columns for a minute and execute again. I will say it again: Always follow processing order. First the source data set gets evaluated in the FROM clause. This means that the Staff table gets wrapped in a gift package and handed over to the SELECT clause. The SELECT then evaluates all expressions for each and every row. And this is true whether we use a source column, but it's also true for the string literal SQL IS FUN. Both are evaluated per row of the Staff table. Does it make more sense now? Frequently, our queries require data that spans multiple sources, thus requiring a JOIN. Most of you have been using JOINs for a long time. It's one of the most fundamental features of SQL. In the next video, we will see how JOINs are processed under the covers.

Contents