SQL EXISTS

4 Key excerpts on "SQL EXISTS"

• 

  eBook - ePub

  PostgreSQL Development Essentials

  • Manpreet Kaur, Baji Shaik(Authors)
  • 2016(Publication Date)
  • Packt Publishing

    (Publisher)

  NOT EXISTS subquery is false.

  The syntax for the PostgreSQL EXISTS condition is as follows:

  WHERE EXISTS ( subquery );

  Parameters or arguments

  The subquery is a SELECT statement that usually starts with SELECT * rather than a list of expressions or column names. To increase performance, you could replace SELECT * with SELECT 1 as the column result of the subquery is not relevant (only the rows returned matter).

  Note

  The SQL statements that use the EXISTS condition in PostgreSQL are very inefficient as the subquery is re-run for every row in the outer query's table. There are more efficient ways, such as using joins to write most queries, that do not use the EXISTS condition.

  Let's look at the following example that is a SELECT  statement and uses the PostgreSQL EXISTS condition:

  SELECT * FROM products WHERE EXISTS (SELECT 1 FROM inventory WHERE products.product_id = inventory.product_id);

  This PostgreSQL EXISTS condition example will return all records from the products table where there is at least one record in the inventory table with the matching product_id . We used SELECT 1 in the subquery to increase performance as the column result set is not relevant to the EXISTS

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Professional Microsoft SQL Server 2008 Programming

  • Robert Vieira(Author)
  • 2010(Publication Date)
  • Wrox

    (Publisher)

  This join-based syntax, for example, would have yielded exactly the same results (subject to possible sort differences). So why, then, would we need this new syntax? Performance—plain and simple.

  When you use the EXISTS keyword, SQL Server doesn't have to perform a full row-by-row join. Instead, it can look through the records until it finds the first match and stop right there. As soon as there is a single match, the EXISTS is true, so there is no need to go further. The performance difference here is even more marked than it is with the inner join. SQL Server just applies a little reverse logic versus the straight EXISTS statement. In the case of the NOT we're now using, SQL can still stop looking as soon as it finds one matching record—the only difference is that it knows to return FALSE for that lookup rather than TRUE . Performance wise, everything else about the query is the same.

  Using EXISTS in Other Ways

  If you work around SQL creation scripts much, you will see an oddity preceding many CREATE statements. It will look something like this:

  IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N‘[Sales].[SalesOrderHeader]’) AND OBJECTPROPERTY(id, N‘IsUserTable’) = 1) DROP TABLE [Sales].[ SalesOrderHeader] GO CREATE TABLE [Sales].[ SalesOrderHeader] ( … …

  You may see variants on the theme—that is, they may use sys.objects , sys.databases , or the INFORMATION_SCHEMA views—but the concept is still the same: They are testing to see whether an object exists before performing a CREATE . Sometimes they may just skip the CREATE if the table already exists, and sometimes they may drop it (as I did in the preceding example). The idea is pretty simple though—they want to skip a potential error condition (the CREATE would error out and blow up your script if the table already existed).

  Just as a simple example, we'll build a little script to create a database object. We'll also keep the statement to a minimum since we're interested in the EXISTS rather than the CREATE

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Relational Database Design and Implementation

  • Jan L. Harrington(Author)
  • 2016(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  are (in this example, customers with sales after 1-Aug-2021). The EXCEPT operator then removes all rows from the first table that appear in the second.

  The second syntax retrieves all columns from both source tables but uses the CORRESPONDING BY clause to project the columns to make the two tables union compatible.

  The EXISTS Operator

  The EXISTS operator check the number of rows returned by a subquery. If the subquery contains one or more rows, then the result is true and a row is placed in the result table; otherwise, the result is false and no row is added to the result table.

  For example, suppose the rare book store wants to see the titles of books that have been sold. To write the query using EXISTS, you would use

  The preceding is a correlated subquery . Rather than completing the entire subquery and then turning to the outer query, the DBMS processes the query in the following manner:

  1. Look at a row in book .

  2. Use the ISBN from that row in the subquery’s WHERE clause.

  3. If the subquery finds at least one row in volume with the same ISBN, place a row in the intermediate result table. Otherwise, do nothing.

  4. Repeat steps 1 through 3 for all rows in the book table.

  5. Join the intermediate result table to work .

  6. Project the title column.

  The important thing to recognize here is that the DBMS repeats the subquery for every row in book . It is this repeated execution of the subquery that makes this a correlated subquery.

  When you are using the EXISTS operator, it doesn’t matter what follows SELECT in the subquery. EXISTS is merely checking to determine whether any rows are present in the subquery’s result table. Therefore, it is easiest simply to use * rather than to specify individual columns.3

  How will this query perform? It will probably perform better than a query that joins book and volume , especially if the two tables are large. If you were to write the query using an IN subquery—

  —you would be using an uncorrelated subquery that returned a set of ISBNs that the outer query searches. The more rows returned by the uncorrelated subquery, the closer the performance of the EXISTS and IN queries will be. However, if the uncorrelated subquery returns only a few rows, it will probably perform better than the query containing the correlated subquery.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Microsoft SQL Server 2012 Programming

  • Paul Atkinson, Robert Vieira(Authors)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  Database already exists. Skipping CREATE DATABASE Statement

  So, without much fanfare or fuss, you’ve added a rather small script that makes things much more usable for the installers of your product. That may be an end user who bought your off-the-shelf product, or it may be you — in which case it’s even better that it’s fully scripted.

  The long and the short of it is that EXISTS is a very handy keyword indeed. It can make some queries run much faster, and it can also simplify some queries and scripts.

  NOTE A word of caution here — this is another one of those places where it’s easy to get trapped in “traditional thinking.” While EXISTS blows other options away in a large percentage of queries where EXISTS is a valid construct, that’s not always the case. For example, the query used as a derived table example can also be written with a couple of EXISTS operators (one for each product), but the derived table happens to run more than twice as fast. That’s definitely the exception, not the rule — EXISTS normally smokes a derived table for performance. Just remember that rules are sometimes made to be broken.

  MIXING DATA TYPES: CAST AND CONVERT

  You’ll see both CAST and CONVERT used frequently. Indeed, you’ve seen both of these very briefly already in this chapter. Considering how often you’ll use these two functions, this seems like a good time to look a little closer at what they can do for you.

  Both CAST and CONVERT perform data type conversions for you. In most respects, they both do the same thing, with the exception that CONVERT also does some date-formatting conversions that CAST

  Sign up to read

  Learn more about book