Check If Two "Select"S Are Equivalent

Check if two selects are equivalent

If you want to compare the query results try the following:

(select * from query1 MINUS select * from query2) 
UNION ALL
(select * from query2 MINUS select * from query1)

This will result in all rows that are returned by only one of the queries.

How to check whether results of two selects are equal in SQL Server?

You may use a CASE expression:

SELECT
    CASE WHEN (SELECT 1) = (SELECT 2)
         THEN 'equal' ELSE 'not equal' END AS result;

This assumes that both of the subqueries return scalars, and not actual result sets. If you need to compare result sets, this is another story.

SQL IF two IDs are equal then select

Do self join to the same table

select emp1.employee_ID, emp1.first_name, emp1.manager_id,emp2.first_name as manager_name from employees emp1
left join employees emp2 
on emp1.manager_id=emp2.employee_id;

Check if two lists are equal

Use SequenceEqual to check for sequence equality because Equals method checks for reference equality.

var a = ints1.SequenceEqual(ints2);

Or if you don't care about elements order use Enumerable.All method:

var a = ints1.All(ints2.Contains);

The second version also requires another check for Count because it would return true even if ints2 contains more elements than ints1. So the more correct version would be something like this:

var a = ints1.All(ints2.Contains) && ints1.Count == ints2.Count;

In order to check inequality just reverse the result of All method:

var a = !ints1.All(ints2.Contains)

SQL Server compare results of two queries that should be identical

you can use the except construct to match between the two queries.

select * from (select * from query1) as query1
except
select * from (select * from query2) as query2

EDIT:

Then reverse the query to find differences with query2 as the driver:

select * from (select * from query2) as query2
except
select * from (select * from query1) as query1

How to verify if two tables have exactly the same data?

I would write three queries.

1. An inner join to pick up the rows where the primary key exists in both tables, but there is a difference in the value of one or more of the other columns. This would pick up changed rows in original.

2. A left outer join to pick up the rows that are in the original tables, but not in the backup table (i.e. a row in original has a primary key that does not exist in backup). This would return rows inserted into the original.

3. A right outer join to pick up the rows in backup which no longer exist in the original. This would return rows that have been deleted from the original.

You could union the three queries together to return a single result set. If you did this you would need to add a column to indicate what type of row it is (updated, inserted or deleted).

With a bit of effort, you might be able to do this in one query using a full outer join. Be careful with outer joins, as they behave differently in different SQL engines. Predicates put in the where clause, instead of the join clause can sometimes turn your outer join into an inner join.

How to do select from where x is equal to multiple values?

Put parentheses around the "OR"s:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND
(
    ads.county_id = 2
    OR ads.county_id = 5
    OR ads.county_id = 7
    OR ads.county_id = 9
)

Or even better, use IN:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND ads.county_id IN (2, 5, 7, 9)

Postgresql compare two select result on the same table

I suggest using SELECT in WITH (here documentation).

WITH orders_current_date AS (
    SELECT count(*)
    FROM order
    WHERE ordered_date > (NOW() - INTERVAL '120 minutes')  
          AND order_ordered = current_date)
), orders_interval AS (
    SELECT count(*)/3
    FROM order
    WHERE ordered_date > (NOW() - INTERVAL '2 days' - INTERVAL '120 minutes')  
          AND ordered_date < (NOW() - INTERVAL '2 days')
)
SELECT
       CASE
           WHEN SELECT * FROM orders_current_date > SELECT * FROM orders_interval
                THEN '1'
           ELSE 
                0
       END;

---

Related Topics