Search

Top 60 Oracle Blogs

Recent comments

SQL Server

Subqueries

Subqueries can take the place of column and table references, helping you
to formulate queries that otherwise would be more difficult or less
efficient to express using join operations. 



Read the full post at www.gennick.com/database.

Subqueries

Ninth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


Subqueries are queries within a query – one SELECT nested within another. They can take the place of column and table references, helping you to formulate queries that otherwise would be more difficult or less efficient to express using join operations. 

As Columns

Following is an example of a subquery filling a position typically occupied by a column name or expression. The goal of the query is to list the number of products per subcategory.

SELECT ps.name,
    (SELECT COUNT(*)
    FROM Production.Product p
    WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID) AS sub_count
FROM production.ProductSubcategory ps

The output looks as  follows:

Surprise Attack of the Nulls: Hidden Traps in the NOT IN

Eight in a series of posts in response to Tim Ford's #EntryLevel Challenge.


My Fear Nothing post last month introduced some of the trouble that nulls can cause by generating results that are counterintuitive to what you would expect from a superficial reading of a query. This month we’ll go deeper into the rabbit hole, because the situation with nulls is rather worse than you might think. 

Let’s imagine for a moment that we’re interested in answering this business question:

Surprise Attack of the Nulls: Hidden Traps in the NOT IN

Nulls are tricky, and their effects go beyond query formulation to affect
even the very the business questions you’re able to pose. Business
questions must be formulated with nulls in mind. 



Read the full post at www.gennick.com/database.

Fear Nothing

Null represents the absence of a value in a database column. Null means no value at all, and in that sense null can be thought of as nothing. Should you fear the nothing? Yes, indeed! Because nulls lead to three-valued logic, which is more like a three-headed monster because of all the unintended, counterintuitive, and often just plain wrong results it can cause.

Counterintuitive Results

Following is a contrived example showing how nulls can lead to counterintuitive results. Execute the query and check the number of rows that are returned. Compare to the total number of products. Evidently there is a large quantity of products that are simultaneously not red and not some other color. How can this be?

Fear Nothing

Three-valued logic arising from nulls in the database is like a
triple-headed monster attacking your queries through unintended and often
wrong results. 



Read the full post at www.gennick.com/database.

Cross Joins

Cross joins give all combinations of rows from two tables. One use for them
lies in generating large amounts of data for performance testing or similar
purposes.



Read the full post at www.gennick.com/database.

Cross Joins

Sixth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


Cross joins give all combinations of rows from two tables. They aren't normally useful, but they can be so in the right circumstances. One use for cross joins lies in generating large amounts of data for performance testing or similar purposes.

Exceptional SQL

SQL's union operators can make queries easy to write and intuitive to read
and understand. One of these is the EXCEPT operator that "subtracts" one
set of rows from another. 



Read the full post at www.gennick.com/database.

Exceptional SQL

Fifth in a series of posts in response to Tim Ford's #EntryLevel Challenge.


SQL implements a number of so-called union operators that under the right circumstances can make queries easy to write and intuitive to read and understand. One of these is the EXCEPT operator that "subtracts" one set of rows from another. 

Say for example that you're doing some work on data quality and want to investigate products that your firm has sold without ever having first purchased. What have you sold but never bought? You can answer that question easily by executing the following EXCEPT query: