Compare 2 Column Values in Same Table

How do I compare two columns in the same table?

SELECT * FROM FOO WHERE C1 = C4 should work. Does it not?

If not, are they the same data type and length? You may need to convert.

I don't know about WebSql, but I've seen some db systems that refuse to match if one is a varchar(5) and the other is a varchar(10) even though they hold the same value.
In those systems you have to use something like

 Convert(varchar, 10, FieldName)

to get a match.

Comparing 2 Columns in same table

select
    count(*) as rows_checked,
    sum(col = col2) as rows_matching,
    sum(col != col2) as rows_different
from table

Note the elegant use of sum(condition).

This works because in mysql true is 1 and false is 0. Summing these counts the number of times the condition is true. It's much more elegant than case when condition then 1 else 0 end, which is the SQL equivalent of coding if (condition) return true else return false; instead of simply return condition;.

Comparing values in two columns in same table and creating new column

This is a little tricky because when there is only one row, then you want to keep the original ordering. One method is aggregation with union all:

select least(sender, receiver) as sender, greatest(sender, receiver) as receiver, 1 as exchanged
from t
group by least(sender, receiver), greatest(sender, receiver)
having count(*) = 2
union all
select sender, receiver, 0
from t
where not exists (select 1
                  from t t2
                  where t2.receiver = t.sender and t2.sender = t2.receiver
                 );

How to compare two columns in the same table, and get a distinct one

You can first fetch the default key,values pairs, excluding the ones that have a value for the corresponindg user and then use UNION and fetch the key,values pairs for the user.

A query using NOT IN and a subquery:

SELECT * FROM `options` WHERE `user` IS NULL 
AND `key` NOT IN 
(SELECT `key` FROM `options` WHERE `user`='John')
UNION ALL 
SELECT * FROM `options` WHERE `user`='John'

The same query using JOIN

SELECT o.* FROM `options` o 
LEFT JOIN 
(SELECT `key` FROM `options` WHERE `user`='John') o2
ON o.key=o2.key WHERE o2.key IS NULL AND o.user IS NULL
UNION ALL
SELECT * FROM `options` WHERE `user`='John'

SQL query to compare multiple columns in same table in oracle

You can use MINUS.

if no_data then both are the same, if there are some records - mean that there is a difference between

create table emp as select * from hr.employees;

insert into emp select employee_id+1000, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, 
 decode(department_id ,30,70, department_id)
 from hr.employees;

select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id  
from emp where employee_id <= 1000
minus
select first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id  
from emp where employee_id > 1000;

But you have to list all columns, because if you have eg different dates or ids - they will be compared too. But it's easier to list columns in SELECT clause then write for everyone WHERE condition.
Maybe it will help.

-- or if different tables and want to compare all cols simply do

drop table emp;
create table emp as select * from hr.employees;
    
create table emp2  as 
select employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, 
 decode(department_id ,30,70, department_id) department_id
 from hr.employees;

select * from emp
minus 
select * from emp2;

---- ADD DATE CRITERIA

-- yes, you can add date criteria and using analytical functions check which
-- is newer and which is

older and then compare one to another. like below:

drop table emp;
create table emp as select * from hr.employees;
        
insert into emp 
        select
           employee_id, 
           first_name, 
           last_name, 
           email, 
           phone_number, 
           hire_date+1, 
           job_id, 
           salary, 
           commission_pct, 
           manager_id, 
           decode(department_id ,30,70, department_id)
        from hr.employees;
        
     with data as   --- thanks to WITH you retrieve data only once
            (select employee_id, first_name, last_name, email, phone_number, 
                hire_date, 
                row_number() over(partition by employee_id order by hire_date desc) rn  -- distinguish newer and older record, 
               job_id, salary, commission_pct, manager_id, department_id  
        from emp)
        select employee_id, first_name, last_name, email, phone_number, department_id from data where rn  = 1
        MIUNUS--- find the differences
        select employee_id, first_name, last_name, email, phone_number, department_id from data where rn = 2;

Comparing same columns in the same table

Seems a self join would work...

SELECT T1.*, T2.*
FROM Table1 T1
INNER JOIN table2 T2
 on T1.Column1 = T2.Column1
and T2.column2 = T2.Column2
and T1.Date <> T2.Date

or an exists (faster but access to only T1 data)

SELECT T1.*
FROM Table1 T1
WHERE Exists (Select 1 
              from table1 T2 
              where T1.Column1 = T2.Column1
                and T2.column2 = T2.Column2
                and T1.Date <> T2.Date)

SQL Comparing Two Columns in same table and update in another table

As I understand you have something like linked lists and for each linked list you want to get min and max id.

If you are using SQL Server this should work straight away, otherwise I think you will be able to apply the same method to other rdbms.

Table declaration and test values insert:

CREATE TABLE #t 
(
    Col1 int,
    Col2 int 
)

INSERT INTO #t VALUES (100, 200), (200, 300 ), (300, 400), (400, 500), (700, 900)

And the actual code:

SELECT MIN(Col1) AS Col, MAX(Col2) AS New FROM 
(
    SELECT SUM(SeqIDStart) OVER (ORDER BY Col1 ASC) AS SeqID, * 
    FROM (
        SELECT
            CASE WHEN LAG(B.Col1, 0, NULL) OVER (ORDER BY a.Col1 ASC) IS NULL THEN 1 ELSE 0 END AS SeqIDStart,
            a.col1 AS Col1, a.Col2 AS Col2, B.Col1 AS adjsCol
        FROM
            #t a
        LEFT JOIN
            #t b
        ON a.col1 = b.col2
        WHERE a.Col1 IS NOT NULL
    ) a
) b
GROUP BY SeqID

I hope this will help

How to compare two row in same table and return the data in response using stored procedure

You need to unpivot all the columns, then join each row to every other.

You can either pivot everything manually using CROSS APPLY (VALUES

SELECT
  aId = a.id,
  bId = b.id,
  v.columnName,
  v.value1,
  v.value2
FROM @t a
JOIN @t b
    ON a.id < b.id
 -- alternatively
 -- ON a.id = 1 AND b.id = 2
CROSS APPLY (VALUES
   ('col1', CAST(a.col1 AS nvarchar(100)), CAST(b.col1 AS nvarchar(100))),
   ('col2', CAST(a.col2 AS nvarchar(100)), CAST(b.col2 AS nvarchar(100))),
   ('col3', CAST(a.col3 AS nvarchar(100)), CAST(b.col3 AS nvarchar(100))),
   ('col4', CAST(a.col4 AS nvarchar(100)), CAST(b.col4 AS nvarchar(100)))
) v (ColumnName, Value1, Value2)
WHERE EXISTS (SELECT v.Value1 EXCEPT SELECT v.Value2)
FOR JSON PATH;

The use of WHERE EXISTS (SELECT a.Value1 INTERSECT SELECT a.Value2) means that nulls will get taken into account properly.

Or you can use SELECT t.* FOR JSON and unpivot using OPENJSON

WITH allValues AS (
    SELECT
      t.id,
      j2.[key],
      j2.value,
      j2.type
    FROM @t t
    CROSS APPLY (
        SELECT t.*
        FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
    ) j1(json)
    CROSS APPLY OPENJSON(j1.json) j2
    WHERE j2.[key] <> 'id'
)
SELECT
  aId = a.id,
  bId = b.id,
  columnName = a.[key],
  value1 = a.value,
  value2 = b.value
FROM allValues a
JOIN allValues b ON a.[key] = b.[key]
    AND a.id < b.id
 -- alternatively
 -- AND a.id = 1 AND b.id = 2
WHERE a.type <> b.type
   OR a.value <> b.value
FOR JSON PATH;

db<>fiddle

SQL Fiddle of actual data