close
The Wayback Machine - https://web.archive.org/web/20240930162327/https://www.geeksforgeeks.org/sql-exists/
Open In App

SQL | EXISTS

Last Updated : 13 Sep, 2023
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

The EXISTS condition in SQL is used to check whether the result of a correlated nested query is empty (contains no tuples) or not. The result of EXISTS is a boolean value True or False. It can be used in a SELECT, UPDATE, INSERT or DELETE statement. Syntax:

SELECT column_name(s) 
FROM table_name
WHERE EXISTS 
  (SELECT column_name(s) 
   FROM table_name
   WHERE condition);

Examples: Consider the following two relation “Customers” and “Orders”. BERJAYA BERJAYA

Queries

  1. Using EXISTS condition with SELECT statement To fetch the first and last name of the customers who placed atleast one order.
SELECT fname, lname 
FROM Customers 
WHERE EXISTS (SELECT * 
              FROM Orders 
              WHERE Customers.customer_id = Orders.c_id);
  1. Output: BERJAYA
  2. Using NOT with EXISTS Fetch last and first name of the customers who has not placed any order.
SELECT lname, fname
FROM Customers
WHERE NOT EXISTS (SELECT * 
                  FROM Orders 
                  WHERE Customers.customer_id = Orders.c_id);
  1. Output: BERJAYA
  2. Using EXISTS condition with DELETE statement Delete the record of all the customer from Order Table whose last name is ‘Mehra’.
DELETE 
FROM Orders
WHERE EXISTS (SELECT *
              FROM customers
              WHERE Customers.customer_id = Orders.c_id
              AND Customers.lname = 'Mehra');
SELECT * FROM Orders;
  1. Output: BERJAYA
  2. Using EXISTS condition with UPDATE statement Update the lname as ‘Kumari’ of customer in Customer Table whose customer_id is 401.
UPDATE Customers
SET lname = 'Kumari'
WHERE EXISTS (SELECT *
              FROM Customers
              WHERE customer_id = 401);
SELECT * FROM Customers;
  1. Output: BERJAYA

Previous Article
Next Article

Similar Reads

SQL vs NO SQL vs NEW SQL
SQL stands for Structured Query Language. Which is based on relational algebra and schema is fixed in this which means data is stored in the form of columns and tables. SQL follows ACID properties which means Atomicity, Consistency, Isolation, and Durability are maintained. There are three types of languages present in SQL : Data Definition Languag
2 min read
SQL | Difference between functions and stored procedures in PL/SQL
Prerequisite: Procedures in PL/SQLFunctions in PL/SQL.Difference between functions and stored procedures in PL/SQL Differences between Stored procedures(SP) and Functions(User-defined functions (UDF)): 1. SP may or may not return a value but UDF must return a value. The return statement of the function returns control to the calling program and ret
3 min read
Difference between T-SQL and PL-SQL
1. Transact SQL (T-SQL) : T-SQL is an abbreviation for Transact Structure Query Language. It is a product by Microsoft and is an extension of SQL Language which is used to interact with relational databases. It is considered to perform best with Microsoft SQL servers. T-SQL statements are used to perform the transactions to the databases. T-SQL has
3 min read
BULK INSERT in SQL Server(T-SQL command)
BULK INSERT in SQL Server(T-SQL command): In this article, we will cover bulk insert data from csv file using the T-SQL command in the SQL server and the way it is more useful and more convenient to perform such kind of operations. Let's discuss it one by one.  ConditionSometimes there is a scenario when we have to perform bulk insert data from .cs
3 min read
Difference between SQL and T-SQL
SQL (Structured Query Language) is the standard language for managing and manipulating relational databases, enabling operations like querying, updating, and deleting data. T-SQL (Transact-SQL), an extension of SQL developed by Microsoft, adds advanced features and procedural capabilities specifically for SQL Server. In this article, We will learn
4 min read
NULL values in SQL
In SQL there may be some records in a table that do not have values or data for every field and those fields are termed as a NULL value. NULL values could be possible because at the time of data entry information is not available. So SQL supports a special value known as NULL which is used to represent the values of attributes that may be unknown o
4 min read
Dirty Read in SQL
Pre-Requisite - Types of Schedules, Transaction Isolation Levels in DBMS A Dirty Read in SQL occurs when a transaction reads data that has been modified by another transaction, but not yet committed. In other words, a transaction reads uncommitted data from another transaction, which can lead to incorrect or inconsistent results. This situation can
6 min read
SQL | Arithmetic Operators
Prerequisite: Basic Select statement, Insert into clause, Sql Create Clause, SQL Aliases We can use various Arithmetic Operators on the data stored in the tables. Arithmetic Operators are: + [Addition] - [Subtraction] / [Division] * [Multiplication] % [Modulus] Addition (+) : It is used to perform addition operation on the data items, items include
5 min read
Reverse a number in PL/SQL
Prerequisite - PL/SQL introduction In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Explanation: Consider the example, input = 12345. Step 1 : mod(12345,10) = 5 rev:= 0*10 + 5 = 5 num = flo
2 min read
SQL Indexes
An index is a schema object. It is used by the server to speed up the retrieval of rows by using a pointer. It can reduce disk I/O(input/output) by using a rapid path access method to locate data quickly. An index helps to speed up select queries and where clauses, but it slows down data input, with the update and the insert statements. Indexes can
5 min read
Difference between Static and Dynamic SQL
Static or Embedded SQL are SQL statements in an application that do not change at runtime and, therefore, can be hard-coded into the application. Dynamic SQL is SQL statements that are constructed at runtime; for example, the application may allow users to enter their own queries. Dynamic SQL is a programming technique that enables you to build SQL
2 min read
Difference between Simple and Complex View in SQL
A View in SQL as a logical subset of data from one or more tables. Views are used to restrict data access. A View contains no data of its own but it is like a window through which data from tables can be viewed or changed. The table on which a View is based is called BASE Tables. There are 2 types of Views in SQL: Simple View and Complex View. Simp
2 min read
SQL | Creating Roles
A role is created to ease the setup and maintenance of the security model. It is a named group of related privileges that can be granted to the user. When there are many users in a database it becomes difficult to grant or revoke privileges to users. Therefore, if you define roles: You can grant or revoke privileges to users, thereby automatically
3 min read
PL/SQL Introduction
PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements.All the statements of a block are passed to oracle engine all at once which increases processing speed and decreases the traffic. Basics of PL/SQL•PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL
6 min read
Factorial of a number in PL/SQL
Prerequisite - PL/SQL introduction In PL/SQL code groups of commands are arranged within a block. A block group related declarations or statements. In declare part, we declare variables and between begin and end part, we perform the operations. Here, first, we take three variables num, fact, and temp and assign the value in num variable (i.e which
1 min read
SQL Trigger | Book Management Database
Prerequisite - SQL Trigger | Student Database For example, given Library Book Management database schema with Student database schema. In these databases, if any student borrows a book from library then the count of that specified book should be decremented. To do so, Suppose the schema with some data, mysql> select * from book_det; +-----+-----
2 min read
Difference between SQL and PLSQL
Introduction SQL: Structured Query Language (SQL) is a standard Database language that is used to create, maintain and retrieve the relational database. The advantages of SQL are: SQL could be a high-level language that has a larger degree of abstraction than procedural languages.It enables the systems personnel end-users to deal with several datab
3 min read
Difference between MySQL and MS SQL Server
SQL is an acronym for Structured Query Language. It is used to access, manipulate and retrieve information from a database. MySQL is an open source Relational Database Management System (RDBMS) based on Structured Query Language (SQL). It runs on platforms like Linux, UNIX and Windows. SQL Server is owned and developed by Microsoft Corporation. The
2 min read
Comparisons between Oracle vs SQL Server
Among several electronic database Management System, the 2 most well-liked and widely used are Oracle and SQL Server. Oracle supports operating systems such as Windows, Linux, Solaris, HP-UX, OS X, etc. Whereas SQL Server supports only windows and Linux operating systems. There are several basic variations between Oracle and SQL Server one of the f
2 min read
What is Temporary Table in SQL?
Temporary Tables are most likely as Permanent Tables. Temporary Tables are Created in TempDB and are automatically deleted as soon as the last connection is terminated. Temporary Tables helps us to store and process intermediate results. Temporary tables are very useful when we need to store temporary data. The Syntax to create a Temporary Table is
2 min read
What is Cursor in SQL ?
Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database Server at the Time of Performing DML(Data Manipulation Language) operations on the Table by the User. Cursors are used to store Database Tables. There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors. These are explained as following below. Implicit Cu
6 min read
Query Execution Engine in SQL
In this article, we will be discussing Query-Execution Engine in SQL and all aspects when we run any query. When we run any query to fetch the data from the database then at the same time many things happen simultaneously to get the desired results. Let's discuss what happens when we submit a query in brief and then we will discuss all phases of qu
3 min read
Strategies For Migrating From SQL to NoSQL Database
Migrating from a SQL database to a NoSQL database can be a complex process, but there are several strategies that can be used to make the transition smoother. Here are some common strategies for migrating from SQL to NoSQL: Analyze the current SQL schema and data model: Before starting the migration, it's important to analyze the current SQL schema
7 min read
How To Develop A Standard SQL Suite For A Robust Streaming Process?
In the current era of digitization, businesses need real-time data analysis and management to stay ahead of the curve. SQL has been the forefront for such data streaming analysis and management. Yet, there are certain limitations to it, which limits the streaming process. Businesses use a combination of tables and streams along with historical data
6 min read
Features of Structured Query Language (SQL)
Here are some key features of Structured Query Language (SQL): Data Definition Language (DDL): SQL provides a set of commands to define and modify the structure of a database, including creating tables, modifying table structure, and dropping tables.Data Manipulation Language (DML): SQL provides a set of commands to manipulate data within a databas
4 min read
Characteristics of SQL
Structured Query Language (SQL) is a standard language to write queries. It was developed under R Project by IBM. SQL has a basic grammar and syntax. It was declared as a standard language to use by American Standard National Institute(ANSI) and International Standard Organization(ISO). The functionality of SQL language is virtually similar across
2 min read
Difference between JOIN and UNION in SQL
Pre-requisites: JOIN, UNION JOIN in SQL is used to combine data from many tables based on a matched condition between them. The data combined using the JOIN statement results in new columns. Consider the two tables: Boys Girls Example: sql> SELECT Boys.Name, Boys.Age, Girls.Address, FROM Boys INNER JOIN Girls ON Boys.Rollno = Girls.Rollno; The r
2 min read
Difference between COMMIT and ROLLBACK in SQL
1. COMMIT-COMMIT in SQL is a transaction control language that is used to permanently save the changes done in the transaction in tables/databases. The database cannot regain its previous state after its execution of commit. Example: Consider the following STAFF table with records: STAFF sql> SELECT * FROM Staff WHERE Allowance = 400; sql> CO
2 min read
Advantages and Disadvantages of SQL
Structural Query Language (SQL) is a powerful and widely used programming language designed for managing and manipulating relational databases. It was first developed in the 1970s by IBM researchers, and has since become a standard language for managing and querying databases across various platforms and industries. SQL enables users to perform com
4 min read
Lateral Keyword in SQL
The lateral keyword represents a lateral join between two or more tables. It joins the output of the outer query with the production of the underlying lateral subquery. It is like a for-each loop in SQL where the subquery iterates through each row of the concerned table, evaluating the subquery for each row. The output rows returned by the inner su
3 min read
Article Tags :