Popular Posts

Wednesday, March 18, 2020

How to except in sql server

No comments :
The SQL Server EXCEPT compares the result sets of two queries and returns the distinct rows from the first query that are not output by the second query. In other words, the EXCEPT subtracts the result set of a query from another.
The following shows the syntax of the SQL Server EXCEPT:
The following are the rules for combining the result sets of two queries in the above syntax:
  • The number and order of columns must be the same in both queries.
  • The data types of the corresponding columns must be the same or compatible.
The following picture shows the EXCEPT operation of the two result sets T1 and T2:
SQL Server EXCEPT illustration
In this illustration:
  • T1 result set includes 1, 2, and 3.
  • T2 result set includes 2, 3, and 4.
The except of  the T1 and T2 returns 1 which is the distinct row from the T1 result set that does not appear in the T2 result set.

SQL Server EXCEPT example

See the following products and order_items tables from the sample database:

A) Simple EXCEPT example

The following example uses the EXCEPT operator to find the products that have no sales:
SQL Server EXCEPT example
In this example, the first query returns all the products. The second query returns the products that have sales. Therefore, the result set includes only the products that have no sales.

B) EXCEPT with ORDER BY example

To sort the result set created by the EXCEPT operator, you add the ORDER BY clause in the last query. For example, the following example finds the products that had no sales and sorts the products by their id in ascending order:
SQL Server EXCEPT with ORDER BY example
In this tutorial, you have learned how to use the SQL Server EXCEPT to combine result sets of two queries.

How to insertset in sql server

No comments :

Introduction to SQL Server INTERSECT

The SQL Server INTERSECT combines result sets of two or more queries and returns distinct rows that are output by both queries.
The following illustrates the syntax of the SQL Server INTERSECT:
Similar to the UNION operator, the queries in the syntax above must conform to the following rules:
  • Both queries must have the same number and order of columns.
  • The data type of the corresponding columns must be the same or compatible.
The following picture illustrates the INTERSECT operation:SQL Server INTERSECT Illustration
In this illustration, we had two result sets T1 and T2:
  • T1 result set includes 1, 2, and 3.
  • T2 result set includes 2, 3, and 4.
The intersection of T1 and T2 result sets returns the distinct rows which are 2 and 3.

SQL Server INTERSECT example

Consider the following query:
SQL Server INTERSECT example
The first query finds all cities of the customers and the second query finds the cities of the stores. The whole query, which uses INTERSECT, returns the common cities of customers and stores, which are the cities output by both input queries.
Notice that we added the ORDER BY clause to the last query to sort the result set.
In this tutorial, you have learned how to use the SQL Server INTERSECT  operator to return the intersection of the result sets of two queries.

How to union in sql server

No comments :

Introduction to SQL Server UNION

SQL Server UNION is one of the set operations that allows you to combine results of two SELECT statements into a single result set which includes all the rows that belongs to the SELECT statements in the union.
The following illustrates the syntax of the SQL Server UNION:
The following are requirements for the queries in the syntax above:
  • The number and the order of the columns must be the same in both queries.
  • The data types of the corresponding columns must be the same or compatible.
The following Venn diagram illustrates how the result set of the T1 table unions with the result set of the T2 table:
SQL Server UNION Venn Diagram

UNION vs. UNION ALL

By default, the UNION operator removes all duplicate rows from the result sets. However, if you want to retain the duplicate rows, you need to specify the ALL keyword explicitly as shown below:
In other words, the UNION  operator removes the duplicate rows while the UNION ALL operator includes the duplicate rows in the final result set.

UNION vs. JOIN

The join such as INNER JOIN or LEFT JOIN combines columns from two tables while the UNION combines rows from two queries.
In other words, join appends the result sets horizontally while union appends result set vertically.
The following picture illustrates the main difference between UNION and JOIN:
SQL Server UNION vs JOIN

SQL Server UNION examples

See the following staffs and customers tables from the sample database:
 

UNION and UNION ALL examples

The following example combines names of staffs and customers into a single list:
SQL Server UNION example
It returns 1,454 rows.
The staffs table has 10 rows and the customers table has 1,445 rows as shown in the following queries:
Because the result set of the union returns only 1,454 rows, it means that one duplicate row was removed.
To include the duplicate row, you use the UNION ALL as shown in the following query:
The query returns 1,455 rows as expected.

UNION and ORDER BY example

To sort the result set returned by the UNION operator, you place the ORDER BY clause in the last query as follows:
For example, to sort the first names and last names of customers and staffs, you use the following query:
In this tutorial, you have learned how to use the SQL Server UNION to combines rows from multiple queries into a single result set.