How to Select a Database in SQL

Published : 09 December 2024
Updated : 12 December 2024

191 Views

Selecting a database means telling the DBMS which database you are going to perform operations. In SQL, selecting a database is a first step task before performing any operations on it. Once the database is selected, we can perform various operations on it, such as creating tables, inserting data, reading data, updating records and deleting records.

This is typically done by using the USE command in many database management systems, such as MySQL, PostgreSQL, SQL Server and others.

Syntax

USE DatabaseName;

Here, the DatabaseName is the name of the database that we want to select for performing our operations.

Example :-

If you are planning to perform some operations on Users database where more than one database present. Below query is used to select/switch the current database.

USE Users;

Why Database Selection is Important

When working with multiple databases. It's crucial to select the correct database before performing any queries on the database. Let's understand this concept with an example.

Let’s consider, we are working with two databases: Movies and Reels. Each database has a table with the same name Videos, but the column of these tables is slightly different.

Movies Database

CREATE DATABASE Movies;
USE Movies;
CREATE TABLE Videos(
  Id INT PRIMARY KEY AUTO_INCREMENT,
  Title varchar(200) NOT NULL,
  Description varchar(500),
  Url varchar(300) NOT NULL,
  DirectorName varchar(100) NOT NULL,
  ProducerName varchar(100) NOT NULL
);

Reels Database

CREATE DATABASE Reels;
USE Reels;
CREATE TABLE Videos(
  Id INT PRIMARY KEY AUTO_INCREMENT,
  Title varchar(200) NOT NULL,
  Description varchar(500),
  Url varchar(300) NOT NULL,
  UserName varchar(100) NOT NULL
);

Requirement :- Let’s assume our requirement is to retrieve the video details from the Reels database by Id.

Problem of Not Selecting a Database

If you run a select query without selecting a database like

SELECT * FROM Videos;

You will encounter an error, as the database context is not set.

Problem of Not Selecting the Correct Database Or Problem of Selecting Wrong Database

If you select the wrong database like Movies in this case, you will get the wrong data.

USE Movies;
SELECT * FROM Videos;

Here you will get data from the Movies database, not the Reels database. If the same id exists in Movies, Videos table you will get wrong data otherwise null.

Correct Way of Selecting a Database

If you select the right database like Reels in this case, you will get expected data so the right way is first select Reels database after that execute your queries.

USE Reels;
SELECT * FROM Videos;

Importance of Selecting the Database Before Performing Operations

In the above example we explained why selecting the correct database is crucial. Same issue applies when performing INSERT, UPDATE or DELETE operations on databases. If you mistakenly query the wrong database, you could:

Insert data into the wrong database table.
Update records unintentionally into another database.
Delete important data by accident from another database.

Key Characteristics

It is essential to explicitly select the database using the USE statement before executing any query to ensure the correct database is being affected.
By explicitly selecting a database, we reduce the risk of accidentally modifying or querying the wrong database, especially in environments that contain multiple databases.
If no database is selected, the system may not know which database to target for performing operations, which could lead to errors or unexpected behavior.
Each database is isolated from the others in terms of its data and schema. When you are selecting a database, ensure that the operations you perform are isolated to that specific database.
The user executing SQL commands has the necessary permissions for that database. Different databases may have different permission settings for each user.
Before selecting a database for modification (especially for write operations - insertions, updates and deletions) ensure that proper backups are in place to prevent data loss in case of an accidental mistake.