How to Find Matches and Non-matches- Tricky SQL Example

Learn the step-by-step process to compare tables for matching brand codes and store numbers, including how to count matches and non-matches effectively.

1. SQL Query to Find Matching Counts
   1. Create Table1
   2. Create Table2
   3. SQL Query to Find Match Counts
   4. 04. Output
   5. Explanation
2. Final Thoughts
3. References

SQL Query to Find Matching Counts

Create Table1

create table tb1(brand_code string, store_number string);
insert into tb1 values("BC01", "U101"),
("BC02", "U106"),
("BC03", "F102"),
("BC04", "U102"),
("BC05", "U103"),
("BC06", "U104")

Create Table2

create table tb2(brand_code string, store_number string);
insert into tb2 values("BC01", "U106"),
("BC02", "U101"),
("BC03", "F102"),
("BC05", "U103")

SQL Query to Find Match Counts

-- Step 1: Get matching brand codes and store numbers
WITH matching_data AS (
    SELECT 
        t1.brand_code,
        t1.store_number AS store_number_table1,
        t2.store_number AS store_number_table2
    FROM tb1 t1
    INNER JOIN tb2 t2
        ON t1.brand_code = t2.brand_code
)
-- Step 2: Get matching and non-matching counts by brand code and store numbers
SELECT 
    brand_code,
    store_number_table1,
    store_number_table2,
    CASE WHEN store_number_table1 = store_number_table2 THEN 1 ELSE 0 END AS is_match
FROM matching_data;

04. Output

brand_code	store_number_table1	store_number_table2	is_match
BC01	        U101	                       U106	           0
BC02	        U106	                       U101	           0
BC03	        F102	                       F102	           1
BC05	        U103	                       U103	           1

Explanation

1. Step 1: matching_data CTE
   • Joins table1 and table2 on brand_code.
   • Selects brand_code and store numbers from both tables.
2. Step 2: Count Matches and Non-Matches
   • Uses CASE WHEN to count the rows where store_number matches or doesn’t match between the two tables.

Final Thoughts

Users can use these references to enhance their understanding of SQL within the Databricks environment and apply best practices to optimize their queries.

References

• Databricks SQL Documentation