SQL UNION

4 Key excerpts on "SQL UNION"

• 

  eBook - ePub

  Relational Database Design and Implementation

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

    (Publisher)

  1  This holds true only if a DBMS has implemented the newer join syntax according to the SQL standard. A DBMS may support the syntax without its query optimizer using the order of tables in the FROM clause to determine join order.

  2  Even a subquery may not avoid joins. Some query optimizers actually replace subqueries with joins when processing a query.

  Passage contains an image

  Chapter 18

  Advanced Retrieval Operations

  Abstract

  This chapter covers advanced retrieval operations such as unions, negative queries, special operators (EXISTS, EXCEPT, AND INTERSECTION), arithmetic in SQL queries, string manipulation, and date and time manipulation.

  Keywords

  SQL SQL retrieval SQL UNION SQL EXISTS SQL EXCEPT SQL INSTERSECT SQL arithmetic SQL string manipulation SQL date manipulation SQL time manipulation

  To this point, the queries you have read about combine and extract data from relations in relatively straightforward ways. However, there are additional operations you can perform on relations that, for example, answer questions such as “show me the data that are not …” or “show me the combination of data that are …”. In this chapter, you will read about the implementation of additional relational algebra operations in SQL that will perform such queries, as well as performing calculations and using functions that you can use to obtain information about the data you retrieve.

  Union

  Union is one of the few relational algebra operations whose name can be used in a SQL query. When you want to use a union, you write two individual SELECT statements joined by the keyword UNION:

  The columns retrieved by the two SELECTs must have the same data types and sizes and be in the same order. For example, the following is legal as long as the customer numbers are the same data type (for example, integer), and the customer names are the same data type and length (for example, 30-character strings):

  Notice that the source tables of the two SELECTs don’t need to be the same, nor do the columns need to have the same names. However, the following is not legal:

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL Injection Attacks and Defense

  • Justin Clarke-Salt(Author)
  • 2009(Publication Date)
  • Syngress

    (Publisher)

  SELECT statements. Its basic syntax is as follows:

  SELECT column-1,column-2,…,column-N FROM table-1 UNION SELECT column-1,column-2,…,column-N FROM table-2

  This query, once executed, will do exactly what you think: It will return a table that includes the results returned by both SELECT statements. By default, this will include only distinct values. If you want to include duplicate values in the resultant table, you need to slightly modify the syntax:

  SELECT column-1,column-2,…,column-N FROM table-1 UNION ALL SELECT column-1,column-2,…,column-N FROM table-2

  The potential of this operator in an SQL injection attack is evident: If the application returns all the data returned by the first (original) query, by injecting a UNION followed by another arbitrary query you can read any table to which the database user has access. Sounds easy, doesn’t it? Well, it is, but there are a few rules to follow, which will be explained in the following subsections.

  Matching Columns

  To work properly, the UNION operator needs the following requirements to be satisfied:

  • The two queries must return exactly the same number of columns.

  • The data in the corresponding columns of the two SELECT statements must be of the same (or at least compatible) types.

  If these two constraints are not satisfied, the query will fail and an error will be returned. The exact error message, of course, depends on which database server technology is used at the back-end, which can be useful as a fingerprinting tool in case the Web application returns the whole message to the user. Table 4.4 contains a list of the error messages that some of the major database servers return when a UNION

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL in 7 Days

  A Quick Crash Course in Manipulating Data, Databases Operations, Writing Analytical Queries, and Server-Side Programming (English Edition)

  • Alex Bolenok(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  we do not document or guarantee but do anyway ".

  Modern database systems support multi-core processors and parallel algorithms for query execution. They can and will execute the two queries in parallel, interleaving their output records as they come out of each process.

  The order will depend on the CPU load, memory latency and other things you cannot predict. You can literally run the same query several times and get the same records in a different order every time.

  If your application logic relies on the order of tuples in the query output, always use ORDER BY .

  The operators UNION and DISTINCT are quite similar.

  DISTINCT makes a set out of a single resultset by removing all duplicates, while UNION makes a set out of a sum of two resultsets, also by removing the duplicates.

  UNION and UNION ALL can add three or more resultsets. We can put UNION or UNION ALL between the queries as many times as we need.

  Let us remember the syntax of SELECT without FROM , which lets us generate resultsets out of thin air, without tables.

  We can use it to better understand the syntax of multiple UNION :

  SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 3; Query 98 ?column? ---------- 1 3 2

  Same query with UNION ALL :

  SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 3; Query 99 ?column? ---------- 1 2 3 3

  As expected, UNION removes duplicates, and UNION ALL preserves them.

  If we wanted to add four numbers, we would add three plus signs between them, like this: 1 + 2 + 3 + 3. Likewise, if we want to add the resultsets of four queries, we add three UNION operators between them.

  As we remember, the resultsets are typed. Every tuple has fields, and every field has a type. And we cannot have tuples with different field counts, names, or types within a resultset. This is the reason we cannot just add any two arbitrary resultsets. They need to be set arithmetic compatible.

  Sign up to read

  Learn more about book
• 

  eBook - PDF

  Beginning SQL

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

    (Publisher)

  The first statement is as follows: SELECT FilmName, YearReleased FROM Films It produces a list of film names and years of release. The second SELECT statement is shown here: SELECT LastName, YEAR(DateOfBirth) FROM MemberDetails This statement produces a list of members’ last names and years of birth. The third and final statement is as follows: SELECT City, NULL FROM Location This particular statement produces a list of city names, and in the second column, it simply produces the value NULL. That completes the look at the UNION operator, and you should know how to produce one single set of results based on the results of more than one query. While the UNION operator is in some ways similar to joins, the biggest difference is that each query is totally separate; there’s no link between them. 232 Chapter 7 Summary This chapter advanced your knowledge of joins far beyond the simple inner joins covered in Chapter 3. You can now answer questions that were impossible to answer with a basic inner join. The chapter started off by revisiting inner joins. It looked at how to achieve multiple joins with multiple conditions. Additionally, this chapter covered the difference between equijoins, which use the equals operator, and non-equijoins, which use comparison operators such as greater than or less than. The chapter then turned to outer joins. Whereas inner joins require the ON condition to return true, outer joins don’t. The chapter covered the following types of outer joins: ❑ Left outer joins, which return rows from the table on the left of the join, whether the ON clause is true or not ❑ Right outer joins, which return rows from the table on the right, regardless of the ON clause ❑ Full outer joins, which return rows from the tables on both sides, even if the ON clause is false In the final part of the chapter, you learned about how you can use the UNION statement to combine the results from two or more SELECT queries into just one set of results.

  Sign up to read

  Learn more about book