I want to compare row count of two tables and then return Show I am thinking of something like this but can't move ahead and need some help.
I am getting multiple rows instead of a single row with asked Dec 11, 2014 at 8:33
you have to remove :
Otherwise it will consider the result of the Case-When for each row of this FROM clause. answered Dec 11, 2014 at 8:41
BanovBanov 2873 silver badges15 bronze badges 0 Or Simply remove the CASE WHEN CLAUSE and write:
as a boolean result will be returned. answered Jan 19, 2018 at 4:33
0 If you are using Oracle db use answered Nov 25, 2016 at 15:46
Say you have requirement to compare two tables. You have two tables in same database or server that you wish to compare, and check if any changes in the column values or see if any row is missing in either of tables. Below are some of the methods you can use to compare two tables in SQL. Compare Two Tables using UNION ALLUNION allows you to compare data from two similar tables or data sets. It also handles the NULL values to other NULL values which JOIN or WHERE clause doesn’t handle. It allows quickly checking what are the data missing or changed in either table.
The above query returns the all rows from both tables as old and new. You can quickly verify the differences between two tables. Related reading: Steps to Optimize SQL Query Performance Compare Two Table using MINUSYou can compare the two similar tables or data sets using MINUS operator. It returns all rows in table 1 that do not exist or changed in the other table.
You can quickly check how many records are having mismatch between two tables. The only drawback with using UNION and MINUS is that the tables must have the same number of columns and the data types must match. Compare Two Table using JOINThis is the easiest but user has to do some additional work to get the correct result. In this approach you can join the two tables on the primary key of the two tables and use case statement to check whether particular column is matching between two tables.
The only drawback of using JOIN is, it cannot compare the NULL values, and you should use the NVL on the column that may have null values in it. Compare Two Table using NOT EXISTSThe other faster method is to use the NOT EXISTS in WHERE clause of the query. This method is faster and performs well on the large volume of data.
In this approach also you have to use the NVL on the columns which contains NULL in it. Compare Cells From Two Tables – Cell by Cell ValidationYou can use the CASE statement to compare records from both tables and check if the cells are matching from both tables. For example, following example demonstrates the cell by cell validation.
You can use LEFT OUTER JOIN or INNER JOIN if you know the table count is matching. Get Matched and Unmatched Count from Two TablesYou can use full outer join to get matched and unmatched records or count from two tables which has common columns in it. SELECT Sum(CASE WHEN t1.file_name IS NOT NULL AND t2.file_n IS NOT NULL THEN 1 ELSE 0 END) AS matched_count, Sum( CASE WHEN t1.file_name IS NOT NULL AND t2.file_n IS NOT NULL THEN 0 ELSE 1 END) AS un_matched_count FROM assessment_file_query_mapping AS t1 FULL OUTER JOIN ( SELECT * FROM (SELECT DISTINCT regexp_split_to_table(files, e',') AS file_n FROM job_info) AS a WHERE length(trim(file_n)) <>0 ) AS t2 ON trim(t1.file_name) = trim(file_n); We are checking the matched and unmatched file count from two tables. Example uses regular expression to split the comma separated records into table and then join on common file name column. Related Posts:
How do I count the number of two tables in SQL?To achieve this for multiple tables, use the UNION ALL. select sum(variableName. aliasName) from ( select count(*) as yourAliasName from yourTableName1 UNION ALL select count(*) as yourAliasName from yourTableName2 ) yourVariableName; Let us implement the above syntax.
How can I compare two table values in SQL?Using relational operators: SELECT * FROM TableA UNION SELECT * FROM TableB EXCEPT SELECT * FROM TableA INTERSECT SELECT * FROM TableB; Change EXCEPT to MINUS for Oracle.. For Oracle, you need to use parentheses around the UNION, parentheses around the INTERSECT, and (as noted) replace EXCEPT with MINUS. ... . Nice answer.. How do I check if two tables have the same data in SQL?Step 1 - Test for Duplicate Rows on TABLEA. If SELECT DISTINCT * FROM TABLEA. ... . Step 2 - Test for Duplicate Rows on TABLEB. If SELECT DISTINCT * FROM TABLEB. ... . Step 3 - INNER JOIN TABLEA to TABLEB on every column.. How can I compare two database tables?Comparing Database Data. On the SQL menu, point to Data Compare, and then click New Data Comparison. ... . Identify the source and target databases. ... . Select the check boxes for the tables and views that you want to compare.. |