SQL UNION

6 Key excerpts on "SQL UNION"

• 

  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

  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 for Data Analytics

  Harness the power of SQL to extract insights from data, 3rd Edition

  • Benjamin Johnston, Jun Shan, Matt Goldwasser, Upom Malik(Authors)
  • 2022(Publication Date)
  • Packt Publishing

    (Publisher)

  As illustrated in all of these examples, it is quite easy to write the same query using multiple techniques. In the next section, you will learn about unions.

  Unions

  Up till now, in this chapter, you have learned how to join data horizontally. You can use joins to add new columns horizontally. However, you may be interested in putting multiple queries together vertically, that is, by keeping the same number of columns but adding multiple rows. Please see this example for more clarity on this.

  Suppose you wanted to visualize the addresses of dealerships and customers using Google Maps. To do this, you would need the addresses of both customers and dealerships. You could build a query with all customer addresses as follows:

  SELECT   street_address, city, state, postal_code FROM   customers WHERE   street_address IS NOT NULL; You could also retrieve dealership addresses with the following query: SELECT   street_address, city, state, postal_code FROM   dealerships WHERE   street_address IS NOT NULL;

  To reduce complexity, it would be nice if there were a way to assemble the two queries into one list with a single query. This is where the UNION keyword comes into play. You can use the two previous queries and create the following query:

  ( SELECT   street_address, city, state, postal_code FROM   customers WHERE   street_address IS NOT NULL ) UNION ( SELECT   street_address, city, state, postal_code FROM   dealerships WHERE   street_address IS NOT NULL ) ORDER BY   1; This produces the following output:

  Figure 3.15: Union of addresses

  Please note that there are certain conditions that need to be kept in mind when using UNION . Firstly, UNION requires the subqueries to have the same number of columns and the same data types for the columns. If they do not, the query will fail to run. Secondly, UNION technically may not return all the rows from its subqueries. UNION , by default, removes all duplicate rows in the output. If you want to retain the duplicate rows, it is preferable to use the UNION ALL keyword. For example, if both of the previous queries return a row with address values such as '123 Main St', 'Madison', 'WI', '53710', the result of the UNION statement will only contain one record for this value set, but the result of the UNION ALL

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

  Professional Microsoft SQL Server 2008 Programming

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

    (Publisher)

  Figure C.1 .

  Figure C.1

  When dealing with queries that use a union, there are just a few key points:

  • All the unioned queries must have the same number of columns in the SELECT list.
  • The headings returned for the combined result set will be taken only from the first of the queries.
  • The data types of each column in a query must be implicitly compatible with the data type in the same relative column in the other queries.
  • Unlike non-union queries, the default return option for unions is DISTINCT rather than ALL . Unless you use the ALL keyword in your query, only one of any repeating rows will be returned.

  In this case, we are creating two tables from which we will select. We'll then insert three rows into each table, with one row being identical between the two tables. If our query is performing an ALL , then every row (six of them) will show up. If the query is performing a DISTINCT , then it will return only five rows (tossing out one duplicate):

  CREATE TABLE UnionTest1 (    idcol   int       IDENTITY,    col2    char(3), ); CREATE TABLE UnionTest2 (    idcol   int       IDENTITY,    col4    char(3), ); INSERT INTO UnionTest1 VALUES    (‘AAA’); INSERT INTO UnionTest1 VALUES    (‘BBB’); INSERT INTO UnionTest1 VALUES    (‘CCC’); SELECT * FROM UnionTest1; INSERT INTO UnionTest2 VALUES    (‘CCC’); INSERT INTO UnionTest2 VALUES    (‘DDD’); INSERT INTO UnionTest2 VALUES    (‘EEE’); PRINT ‘Regular UNION---------------’ SELECT col2 FROM UnionTest1   UNION SELECT col4 FROM UnionTest2; PRINT ‘UNION ALL-------------------’ SELECT col2 FROM UnionTest1   UNION ALL SELECT col4 FROM UnionTest2; DROP TABLE UnionTest1; DROP TABLE UnionTest2;

  Run it, and the key results look like this:

  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)

  UNION operator, which allows you to combine the results of two queries into one.

  COMBINING TABLE DATA WITH JOINS

  When you’re operating in a normalized environment, you’ll frequently run into situations in which not all of the information that you want is in one table. In other cases, all the information you want returned is in one table, but the information you want to place conditions on is in another table. This is where the JOIN clause comes in.

  A JOIN does just what it sounds like — it joins the information from two tables together into one result set. You can think of a result set as being a virtual table. It has both columns and rows, and the columns have data types. Indeed, in Chapter 7 , I’ll show you how to treat a result set as if it were a table and use it for other queries.

  How exactly does a JOIN put the information from two tables into a single result set? Well, that depends on how you tell it to put the data together — that’s why there are four kinds of JOIN s. The thing that all JOIN s have in common is that they match one record up with one or more other records to make a record that is a superset created by the combined columns of both records.

  For example, take a look at a record from a table called Films :

  FILMID	FILMNAME	YEARMADE
  1	My Fair Lady	1964

  Now follow that up with a record from a table called Actors :

  FILMID	FIRSTNAME	LASTNAME
  1	Rex	Harrison

  With a JOIN , you can create one record from these two records found in totally separate tables:

  This JOIN (at least apparently) joins records in a one-to-one relationship. One Films record joins to exactly one Actors record.

  Let’s expand things just a bit and see if you can see what’s happening. I’ve added another record to the Actors

  Sign up to read

  Learn more about book