Structured Query Language (SQL) is the de-facto query language used in most database management systems (DBMS) such as Oracle and Microsoft SQL Server. This domain-specific language is used in programming to query and return the desired data from a database. We use SQL to write queries that declare what data to expect from a dataset without really indicating how to obtain it. We can also use SQL to update and delete information from a database.
Ideally, in a relational database management system, the database usually runs on the “back end” side of a server in its structured form. By itself, this data is hard to interpret. So, users often have programs on a client computer that help to manipulate that data using rows, columns, fields, and tables. These programs are designed to allow users to send SQL statements to the server. The server then processes these statements by filtering data from the enormous, complex databases and returns results to the user.
Each query begins with finding the data needed then refining it down into something that can be processed and understood easily. To do this, you must use an organized set of operations to get meaningful data from a dataset. This article will explore the Minus Vs except SQL clauses to help you write optimized queries that run fast across various DBMS.
SQL EXCEPT clause
The SQL EXCEPT clause is one of the most commonly used statements that work together with two SELECT statements to return unique rows from a dataset. The SQL EXCEPT combines two SELECT statements to return the row that is present in the first select statement and not in the second.
If you’ve noticed, most SQL clauses do the same thing represented in standard spoken language. For instance, exception literally means not included. SQL EXCEPT is also very similar to the same concept.
The EXCEPT statement returns the distinct row from the left input query that is not output by the right input query. I.e., Returns the resultant rows that appear in query_expression_1 and not in query_expression_2.
SQL EXCEPT Clause Example
Consider a simple situation where you have two tables, one with dog names and the other one with cat names.
Cats Data Set
+———+———–+
| CatId | CatName |
|———+———–|
| 1 | Boss |
| 2 | Scarlet |
| 3 | Fluffy |
| 4 | Fluffy |
+———+———–+
Dogs Data Set
+———+———–+
| DogId | DogName |
|———+———–|
| 1 | Yelp |
| 2 | Woof |
| 3 | Boss |
| 4 | Boss |
+———+———–+
Using the SQL Except statement, we can filter the dataset and return only the distinct rows from the left SELECT query that have not been returned by the SELECT query on the right side of the EXCEPT statement.
An example SQL syntax query would look like this:
SELECT CatName FROM Cats
EXCEPT
SELECT DogName FROM Dogs;
In a typical scenario, a client program will send this query to the “back-end” server. This statement is then processed and only returns the values available in the “cats” dataset that don’t appear in the “dogs” dataset. When two rows are similar, as is the case with “fluffy,” only one row is returned. This is because the SQL query only returns distinct rows.
Here’s the result of the above query:
+———–+
| CatName |
|—————|
| Fluffy |
| Scarlet |
+————– +
Common SQL Except Rules
- You must have the same number of columns in both queries
- The column order must be the same in all queries
- The column data types must be compatible with each other. The data types really don’t have to be the same, but they MUST be comparable through implicit conversion.
- The EXCEPT statement returns all records from the 1st SELECT statement not available in the second SELECT statement
- The EXCEPT operator in the SQL server is similar to the MINUS operator in Oracle.
- MySQL does not support SQL Except clause. The workaround is to use the LEFT JOIN clause when using MySQL.
SQL MINUS Clause
MINUS operator does the same thing as the EXCEPT clause. But unlike the EXCEPT clause, the MINUS operator is only supported by Limit number of databases:
| Database Name | Minus | Except |
| Amazon Redshift | No | Yes |
| VQL (denodo) | Yes | No |
| Elasticsearch | Yes | No |
| MariaDB | Yes | Yes |
| IBM Db2 | No | Yes |
| Microsoft SQL Server | No | Yes |
| MongoDB | No | No |
| MySQL | No | No |
| Oracle | Yes | No |
| PostgreSQL | No | Yes |
| SOQL (salesforce) | No | No |
| snowflake | Yes | Yes |
| SQLite | No | Yes |
The MINUS operator compares two queries and only returns the rows present in the first dataset but are not output by the second set. The result usually contains the distinct rows available in the left Select statement that aren’t included in the results of the right select statement.
Here is a typical MINUS syntax:
SELECT column_list_1 FROM T1
MINUS
SELECT column_list_2 FROM T2;
Common Oracle Minus Operator Rules
For MINUS operator to work, the dataset must conform with rules similar to those of SQL EXCEPT clause:
- The data type of corresponding columns must be similar (Either Numeric or Character)
- The order and number of columns must be the same.
- The column used for ordering can be defined by the column number.
- Duplicates are automatically eliminated in the final result.
Conclusion
The Minus Vs Except SQL Clause comparison can be confusing for many people. These two clauses are synonymous with each other and have similar syntax and results. Both Minus and Except help users skim through datasets to identify unique rows available only in the first SELECT query and not returned by the second SELECT query.
