How to Compare Expression with Result of Subquery in SQL

In this article I am going to explain how to compare expression with result of subquery in SQL.
  • 2375

Introduction

In this article I am going to explain how to compare expression with result of subquery in SQL. A select statement known as subquery if it is codded within another SQL statement. Subquery can return a single value or a result set. This result set may contain a single column or more than one column.

There are four way to create subquery  in a SELECT statement.

  • In WHERE Clause
  • In HAVING Clause
  • In FROM Clause
  • In SELECT Clause

We can compare result set of subquery with an expression using comparison operator in SQL.

Example

SELECT invoiceid,vendorid,invoicetotal
FROM dbo.mcninvoices
WHERE invoicetotal-paymenttotal-creadittotal >0
AND invoicetotal > ( SELECT AVG(invoicetotal)
FROM dbo.mcninvoices
WHERE invoicetotal-paymenttotal-creadittotal >0 )

Output 

Clipboard04.jpg


© 2020 DotNetHeaven. All rights reserved.