---
title: "How to Find Matches and Non-matches- Tricky SQL Example"
description: "Master the technique of comparing two tables to find matching brand codes and store numbers, and accurately count both matches and non-matches for interviews"
url: https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/
date: 2025-01-14
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2025/01/use-color-photo-teaching-sql.png?fit=1024%2C768&ssl=1
categories: ["SQL"]
tags: ["Databricks SQL Example"]
type: post
lang: en
---

# 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](https://srinimf.com/2023/03/14/how-to-use-condition-statement-in-if-logic-in-python/) count matches and non-matches effectively.

1. [SQL Query to Find Matching Counts](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/#sql-query-to-find-matching-counts)
   1. [Create Table1](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/#create-table1)
   2. [Create Table2](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/#create-table2)
   3. [SQL Query to Find Match Counts](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/#sql-query-to-find-match-counts)
   4. [04. Output](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/#04-output)
   5. [Explanation](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/#explanation)
2. [Final Thoughts](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/#final-thoughts)
3. [References](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/#references)

## [SQL Query to](https://srinimf.com/2024/06/18/sql-query-to-find-null-and-non-null-percentage-of-column/) 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.

Related[Understanding SQL LIKE, ILIKE, and RLIKE Operators](https://srinimf.com/2025/01/16/databricks-sql-using-like-ilike-rlike-effectively/)

## References

- [Databricks SQL Documentation](https://docs.databricks.com/sql/index.html)

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2025%2F01%2F14%2Fsql-tutorial-finding-matching-brand-codes%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2025%2F01%2Fuse-color-photo-teaching-sql.png%3Ffit%3D1024%252C768%26ssl%3D1&description=How%20to%20Find%20Matches%20and%20Non-matches-%20Tricky%20SQL%20Example)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2025/01/14/sql-tutorial-finding-matching-brand-codes/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20How%20to%20Find%20Matches%20and%20Non-matches-%20Tricky%20SQL%20Example&body=https%3A%2F%2Fsrinimf.com%2F2025%2F01%2F14%2Fsql-tutorial-finding-matching-brand-codes%2F&share=email)
-

### Like this:

Like Loading…