With PostgreSQL

How do I run multiple SELECT statements in PostgreSQL?

Problem:

You’d like to display data from given columns [of a similar data type] from two tables in SQL.

Example:

There are two tables in our database: employee and customer.

The employee table contains data in the following columns: id, first_name, last_name, and age.

idfirst_namelast_nameage

1	Tom	Miller	22
2	John	Smith	26
3	Lisa	Williams	30
4	Charles	Davis	21
5	James	Moore	22

The customer table contains data in the following columns: id, first_name, last_name, and age.

idfirst_namelast_nameage

1	Milan	Smith	45
2	Charles	Davis	21
3	Mark	Backer	19

In one result set, let’s display the first name, last name, and age for all people in the database, both employees and customers.

Solution 1:

We’ll use UNION ALL to join data from columns in two tables.

Here’s the query you’d write:

SELECT first_name, last_name, age FROM employee UNION ALL SELECT first_name, last_name, age FROM customer;

Here’s the result:

first_namelast_nameage

Tom	Miller	22
John	Smith	26
Lisa	Williams	30
Charles	Davis	21
James	Moore	28
Milan	Smith	45
Charles	Davis	21
Mark	Backer	19

Discussion:

Use the UNION ALL clause to join data from columns in two or more tables. In our example, we join data from the employee and customer tables. On the left of the UNION ALL keyword, put the first SELECT statement to get data from the first table [in our example, the table employee]. On the right, use another SELECT statement to get data from the second table [in our example, customer].

Remember that the selected data in both tables must be of the same data type in each column. For example, if the first column in the first SELECT is a string data type, the first column in the second SELECT must also be a string data type. If the second column in the first SELECT statement is an integer, the second column in the second table must also be an integer type.

In the first query, we selected age [the age of the employee, which is an integer data type] for the third column. Therefore, the third column in the second SELECT is also an integer value; it’s the age of the customer.

The second columns in both SELECT statements are the same data type. However, if the values are the same in both tables, they will be displayed multiple times; for example, ‘Charles Davis 21’ is shown twice in the result set.

What if don’t want multiple identical records in the result table? In this case, use UNION. It is similar to UNION ALL, but it removes duplicate records. Look at the following example.

Solution 2:

Here’s the query that avoids duplicate records:

SELECT first_name, last_name FROM employee UNION SELECT first_name, last_name FROM customer;

Here’s the result of the above query:

first_namelast_name

Mark	Backer
James	Moore
John	Smith
Charles	Davis
Milan	Smith
Tom	Miller
Lisa	Williams

Note:

UNION ALL is faster than UNION, but UNION removes duplicate rows. The choice depends on the result data we need.

Subscribe to our newsletter Join our monthly newsletter to be
notified about the latest posts.

Email address

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query

This topic explains how to submit a request containing multiple statements to the Snowflake SQL API.

In this Topic:

Specifying Multiple SQL Statements in the Request¶

To submit multiple SQL statements in a single request:

In the statement field, use a semicolon [;] between each statement.
In the parameters field, set the MULTI_STATEMENT_COUNT field to the number of SQL statements in the request.

For example:

POST /api/v2/statements HTTP/1.1 Authorization: Bearer Content-Type: application/json Accept: application/json User-Agent: myApplication/1.0 X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT { "statement": "alter session set QUERY_TAG='mytesttag'; select count[*] from mytable", ... "parameters": { "MULTI_STATEMENT_COUNT": "2" } }

In this example MULTI_STATEMENT_COUNT is set to 2 which corresponds to the number of SQL statements being submitted.

To submit a variable number of SQL statements in the statement field, set MULTI_STATEMENT_COUNT to 0. This is useful in an application where the number of SQL statements submitted is not known at runtime.

If the value of MULTI_STATEMENT_COUNT does not match the number of SQL statements specified in the statement field, the SQL API returns the following error:

Actual statement count did not match the desired statement count .

Where

actual_count is the number of statements specified in the statement field.
desired_count is the value of MULTI_STATEMENT_COUNT.

If you specify multiple SQL statements in the statement field, but do not specify the MULTI_STATEMENT_COUNT field, the SQL API returns the following error:

Actual statement count 3 did not match the desired statement count 1.

Getting the Results for Each SQL Statement in the Request¶

If a request that contains multiple SQL statements is processed successfully, the response does not include the data returned from executing the individual statements. Instead, the response contains a statementHandles field that contains an array of the handles for the individual statements.

Note

The statementHandles field is different from the statementHandle field:

The statementHandle field specifies the handle for the set of SQL statements in the request.
The statementHandles field is an array of the handles of the individual SQL statements in the request.

For example, suppose that you send a request that specifies two SQL statements for execution:

The response contains a statementHandles field that contains an array of the handles for the individual statements.

HTTP/1.1 200 OK ... { ... "statementHandles" : [ "019c9fce-0502-f1fc-0000-438300e02412", "019c9fce-0502-f1fc-0000-438300e02416" ], ... }

To check the status and retrieve the data for the individual statements, send a GET request to the /api/v2/statements/ endpoint and append the handle for each statement to the URL path. See Checking the Status of the Statement Execution and Retrieving the Data for details.

GET /api/v2/statements/019c9fce-0502-f1fc-0000-438300e02412 ...

GET /api/v2/statements/019c9fce-0502-f1fc-0000-438300e02416 ...

Handling Errors When Specifying Multiple Statements in a Request¶

If you specified multiple SQL statements in the request and an error occurred when executing any of the statements, Snowflake returns the HTTP response code 422 with a QueryFailureStatus object.

You can get details about the error from this object.

For example, suppose that your request specifies the following statements in which the second INSERT statement contains an error:

{ "statement": "create or replace table table1 [i int]; insert into table1 [i] values [1]; insert into table1 [i] values ['This is not a valid integer.']; insert into table1 [i] values [2]; select i from table1 order by i", ... }

Snowflake returns a response with the HTTP response code 422 and with a QueryFailureStatus object that contains the details about the error:

HTTP/1.1 422 Unprocessable Entity Content-Type: application/json ... { "code" : "100132", "message" : "JavaScript execution error: Uncaught Execution of multiple statements failed on statement \"insert into table1 [i] values ...\" [at line 1, position 75].\nNumeric value 'This is not a valid integer.' is not recognized in SYSTEM$MULTISTMT at ' throw `Execution of multiple statements failed on statement {0} [at line {1}, position {2}].`.replace['{1}', LINES[i]]' position 4\nstackstrace: \nSYSTEM$MULTISTMT line: 10", "sqlState" : "P0000", "statementHandle" : "019d6e97-0502-317e-0000-096d0041f036" }

In the example above, the INSERT statement with the error starts at the character position 75 in the value of the statement field.

The statements before the statement with the error are executed successfully [the CREATE TABLE and first INSERT statement in this example]. The statements after the statement with the error are not executed.

How do you write two select statements in a single query in PostgreSQL?

Procedure.

To combine two or more SELECT statements to form a single result table, use the set operators: UNION, EXCEPT or INTERSECT. ... .

To keep all duplicate rows when combining result tables, specify the ALL keyword with the set operator clause..

How do you run multiple queries in pgAdmin 4?

If you're using pgAdmin 4, you just type everything on the Query Tool pane, and then press the button with a [lightning bolt] [or press F5].

How can I use two SQL queries in one result?

In this step, you create the union query by copying and pasting the SQL statements..

On the Create tab, in the Queries group, click Query Design..

On the Design tab, in the Query group, click Union. ... .

Click the tab for the first select query that you want to combine in the union query..

How do you combine multiple selected statements?

The UNION operator is used to combine the result-set of two or more SELECT statements..

Every SELECT statement within UNION must have the same number of columns..

The columns must also have similar data types..

The columns in every SELECT statement must also be in the same order..