SQL CONNECTION

What is SQL CONNECTION in Standard SQL?

Switches the current session to a different predefined database connection within the same script or client.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL CONNECTION:

SQL CONNECTION Full Explanation

SQL CONNECTION (formally SET CONNECTION in the SQL standard) is a session-level statement that makes an existing, previously declared connection the active one. It lets a script or interactive session hop between multiple databases without opening new network sockets each time. When a new connection becomes active, any uncommitted work on the old connection is implicitly committed or rolled back depending on the DBMS, session variables reset to the new context, and subsequent commands execute against the newly selected database. The command does not create a connection; it merely activates one that has already been established with a CONNECT or DECLARE CONNECTION statement. Because not every database engine exposes multiple session connections natively, support is limited and syntax varies.

SQL CONNECTION Syntax

-- ISO/IEC 9075 (Standard SQL)
SET CONNECTION connection_name;

-- Oracle SQL*Plus and most clients
CONNECT user/password@connect_identifier;

-- SQL Server (sqlcmd utility)
:CONNECT server_name -U user -P password

-- PostgreSQL psql meta-command
\connect dbname user

SQL CONNECTION Parameters

connection_name (identifier or string) - The symbolic name of an existing declared connection to activate.
user (string) - Username to authenticate (Oracle, SQL Server, PostgreSQL client variants).
password (string) - Password for the user (Oracle, SQL Server, PostgreSQL client variants).
server_name (string) - Target server or instance (SQL Server sqlcmd).

Example Queries Using SQL CONNECTION

-- Standard SQL example
DECLARE CONNECTION sales_conn FOR SERVER sales_db;
DECLARE CONNECTION hr_conn    FOR SERVER hr_db;

SET CONNECTION sales_conn;
SELECT COUNT(*) FROM orders;

SET CONNECTION hr_conn;
SELECT COUNT(*) FROM employees;

-- Oracle example
CONNECT hr/hr@prod_db;
SELECT COUNT(*) FROM employees;

CONNECT analytics/ana@bi_db;
SELECT COUNT(*) FROM dashboards;

Expected Output Using SQL CONNECTION

The active session context changes to the specified connection
All subsequent SQL statements run against that connection
Result sets returned depend on the target database objects

Use Cases with SQL CONNECTION

ETL scripts that read from a source database and write to a target database within the same job.
Administrative tooling that rotates through several tenant databases.
Interactive client sessions where an analyst needs to compare metrics across environments without opening new windows.

Common Mistakes with SQL CONNECTION

Attempting to use SET CONNECTION on a DBMS that does not implement it.
Forgetting that open transactions are usually ended when switching connections, potentially causing unintended commits or rollbacks.
Misspelling the declared connection name, resulting in "connection does not exist" errors.
Treating CONNECT as if it merely selects a schema when in fact it reauthenticates and resets session state (Oracle, SQL Server clients).

First Introduced In

SQL-92 (SET CONNECTION)

Frequently Asked Questions

What does SQL CONNECTION mean?

It refers to the SET CONNECTION statement defined in the SQL standard that changes which predefined connection is active in the current session.

How do I declare connections before using SET CONNECTION?

Use DECLARE CONNECTION or a vendor-specific CONNECT command to establish each connection, then reference its symbolic name in SET CONNECTION.

Does changing connections affect session variables?

Yes. Once you switch, variables such as current schema, role, and isolation level reflect the settings of the target connection.

What if my database does not support SET CONNECTION?

You must open separate client sessions or rely on the vendor's alternative, such as USE database (SQL Server) or CONNECT (Oracle).