SQL EXISTS

6 Key excerpts on "SQL EXISTS"

• 

  No longer available |Learn more

  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 - PDF

  Information Modeling and Relational Databases

  From Conceptual Analysis to Logical Design

  • Terry Halpin(Author)
  • 2001(Publication Date)
  • Morgan Kaufmann

    (Publisher)

  Now let’s consider existential subqueries. In predicate logic, the proposition “There is a frog” may be formulated as “ ∃ x Fx “. Here “ ∃ ” is the existential quantifier, with the meaning “there exists at least one”, “ x ” is an individual variable standing for some object, and “ F ... ” abbreviates the predicate “... is a frog”. An existential subquery in SQL is somewhat similar. It appears in an existential condition of the form exists ( subquery ) Here exists is used instead of “ ∃ ” for the existential quantifier, and subquery is ef-fectively a tuple variable ranging over rows returned by the subquery. The exists quantifier may be thought of as a Boolean function, returning true if the subquery re-turns a row and false otherwise. The exists quantifier may be preceded by the logical not operator. The possible cases are shown below. Here the term “a row” means “at least one row”. Since the system always knows whether or not a row is returned, a sim-ple two-valued logic applies—existential conditions evaluate to true or false, never unknown. exists ( subquery ) → True --a row is returned (by the subquery) → False --no row is returned not exists ( subquery ) → True --no row is returned → False --a row is returned Existential subqueries may be used within search conditions in a where clause, lead-ing to overall queries of the following form: select ... from ... where [ not ] exists ( select * ...) Since the mere existence of a row is all that counts in the subquery result, it is normal to simply use “*” instead of a column list in the select-list for the existential subquery. Prior to SQL-92, existential subqueries were the only subqueries that could return mul-tiple columns, since in SQL-89 both membership and comparison subqueries had to re-turn a single column. Some SQL dialects still have this restriction. Let’s look at some examples using the populated schema in Figure 11.61. The Mem-ber table stores details about members of a martial arts club.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Introduction to Database and Knowledge-Base Systems

  • S Krishna(Author)
  • 1992(Publication Date)
  • WSPC

    (Publisher)

  4.1.3 Nested Queries Nesting of queries in SQL was visualized as a feature which would simplify query expression and also improve readability for more complex queries. We may assume, for comprehension, that the system evaluates the 74 Relational Database Languages and Systems inner or nested subquery first and then the outer subquery. The query, how-ever, can be expressed in alternative forms to obtain the same output. Q9: Get date and value of sales made to customer Donald. SELECT SDATE, VALUE FROM SALE WHERE CNO LN (SELECT CNO FROM CUSTOMER WHERE NAME = 'Donald') In the present example, an alternate expression is SELECT SDATE, VALUE FROM SALE, CUSTOMER WHERE SALE.CNO = CUSTOMER.CNO AND CUSTOMER.NAME = 'Donald' Multiple levels of nesting is also possible as in the following: Q10: Obtain details of salesmen who have sold airconditioners. SELECT * FROM SALESMAN WHERE SNO IN (SELECT SNO FROM SALE WHERE PNO LN (SELECT PNO FROM PRODUCT WHERE DESCRIPTION = 'Airconditioner')) This query again may alternatively be expressed in terms of a join of three tables as in Q7. The EXISTS clause represents the existential quantifier of predicate calculus. The negated clause NOT EXISTS is useful in queries which in some form require all or none values. 4.1 SQL 75 In its simple form EXISTS can be used instead of IN for subqueries as in the following rephrasing of query Q10 below. Qll: Obtain details of salesmen who have sold airconditioners. SELECT* FROM SALESMAN WHERE EXISTS (SELECT SNO FROM SALE WHERE PNO IN (SELECT PNO FROM PRODUCT WHERE DESCRIPTION- 'Airconditioner* AND SALE PNO - PRODUCT.PNO AND SALE. SNO = SALESMAN. SNO)) Q12: Get details of customers who have not bought a Washing Machine. SELECT* FROM CUSTOMER WHERE NOT EXISTS (SELECT * FROM SALE WHERE PNO IN (SELECT PNO FROM PRODUCT WHERE CUSTOMER.CNO=SALE.CNO AND SALE.PNO=PRODUCT.PNO AND PRODUCT.DESCRIPTION= 'Washing Machine')) Q13: Get details of customers who have purchased every product.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Beginning SQL

  • Paul Wilton, John Colby(Authors)
  • 2005(Publication Date)
  • Wrox

    (Publisher)

  Notice the use of the asterisk (*), which returns all columns, in the inner subqueries. EXISTS is row- based, not column-based, so it doesn’t matter which columns are returned. The first example uses the following SELECT query as its inner subquery: (SELECT * FROM MemberDetails WHERE MemberId < 5); This subquery provides a results set with three rows. Therefore, EXISTS evaluates to true, and you get the following results: City Orange Town Windy Village Big City The second example uses the following inner subquery: SELECT * FROM MemberDetails WHERE MemberId > 99 This returns no results because no records in MemberDetails have a MemberId greater than 99. Therefore, EXISTS returns false, and the outer SELECT statement returns no results. Finally, the third example uses the following query as its inner subquery: SELECT * FROM MemberDetails WHERE MemberId = 15 This query returns just one row, and even though some of the columns contain NULL values, it’s still a valid row, and EXISTS returns true. In fact, even if the whole row contained NULLs, EXISTS would still return true. Remember, NULL doesn’t mean no value; it means an unknown value. So, the results for the full query in the example are as follows: City Orange Town Windy Village Big City You can reverse the logic of EXISTS by using the NOT operator, essentially checking to see whether no results are returned by the subquery. Modify the second example described previously, where the sub- query returns no results, adding a NOT operator to the EXISTS keyword: SELECT City FROM Location WHERE NOT EXISTS (SELECT * FROM MemberDetails WHERE MemberId > 99); 250 Chapter 8 Now NOT EXISTS returns true, and as a result the WHERE clause is also true. The final results are shown in the following table: City Orange Town Windy Village Big City These examples are very simple and, to be honest, not that realistic in terms of how EXISTS is used.

  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
• 

  eBook - PDF

  A Guide to SQL

  • Mark Shellman, Hassan Afyouni, Philip Pratt, Mary Last, Mark Shellman(Authors)
  • 2020(Publication Date)
  • Cengage Learning EMEA

    (Publisher)

  Editorial review has deemed that any suppressed content does not materially affect the overall learning experience. Cengage Learning reserves the right to remove additional content at any time if subsequent rights restrictions require it. Module 5 140 In Figure 5-5, evaluating the subquery produces a temporary table consisting of those item ID values that are present in invoice number 14233. Executing the remaining portion of the query produces descriptions for each item whose ID is in this temporary table; in this case, Wild Bird Food (25 lb), Quilted Stable Blanket, and Insulated Water Bucket. Using the EXISTS Operator You also can use the EXISTS operator to retrieve data from more than one table, as shown in Example 5. The EXISTS operator checks for the existence of rows that satisfy some criterion. EXAMPLE 5: Find the invoice number and invoice date for each invoice that contains item ID KH81. This query is similar to the one in Example 4, but this time the query involves the INVOICES table and not the ITEM table. In this case, you can write the query in either of the ways previously demonstrated. For example, you could use the IN operator with a subquery, as shown in Figure 5-6. Using the EXISTS operator provides another approach to solving Example 5, as shown in Figure 5-7. The subquery in Figure 5-7 is the first one you have seen that involves a table listed in the outer query. This type of subquery is called a correlated subquery. In this case, the INVOICES table, which is listed in the FROM clause of the outer query, is used in the subquery. For this reason, you need to qualify the INVOICE_NUM column in the subquery (INVOICES.INVOICE_NUM). You did not need to qualify the columns in the previous queries involving the IN operator.

  Sign up to read

  Learn more about book