what is inequality join in mysql,how it works

Question

dealer table

ID | Product
------------
1   product1
1   product2
1   product3
1   product4
2   product1
2   product2
2   product3
3   product1
3   product2
3   product3
4   product1
4   product2

i want to find all id and its top 2 product

my try is

  select d1.id,d1.product,count(d1.product)
  from dealer d1 join
       dealer d2  
       on d1.id=d2.id
  where d1.product>=d2.product
  group by d1.id,d1.product
 having count(d1.product)<=2

my result is

id |product   |count(d1.product) 
----------------------------
1   product1    1
1   product2    2
2   product1    1
2   product2    2
3   product1    1
3   product2    2
4   product1    1
4   product2    2

google search says i got proper result due to inequality join but i want to know how inequality join working in this

Answer 1

To understand how it works, just debug the query, step by step, but using a smaller data set
- for example only for rows with id = 1

select * from dealer;

| ID |  Product |
|----|----------|
|  1 | product1 |
|  1 | product2 |
|  1 | product3 |
|  1 | product4 |

The first evaluated part of the query is a join:

select *
from dealer d1 
join dealer d2  
on d1.id=d2.id AND d1.product>=d2.product
order by 1,2,3,4

How a join works ?
It takes a first record from the left table (d1), then searches through the right table (d2) and pick from the right table all records that meet the join condition.

For the record with product1 (d1 table) there is only one record from d2 table that meets the condition: d1.product>=d2.product - just product1.
So a partial resultset for 1st record from d1 is:

| ID |  Product | ID |  Product |
|----|----------|----|----------|
|  1 | product1 |  1 | product1 |

---

Then the query takes the next record from d1 - product2.
In this case there are 2 records that meet the join condition: product1 and product2.
A partial resultset for 2nd record from d1 is:

| ID |  Product | ID |  Product |
|----|----------|----|----------|
|  1 | product2 |  1 | product1 |
|  1 | product2 |  1 | product2 |

---

Then the query takes the next record from d1 - product3.
In this case there are 3 records that meet the join condition: product1, product2 and product3.
A partial resultset for 3rd record from d1 is:

| ID |  Product | ID |  Product |
|----|----------|----|----------|
|  1 | product3 |  1 | product1 |
|  1 | product3 |  1 | product2 |
|  1 | product3 |  1 | product3 |

---

Then the query takes the last record from d1 - product4.
In this case there are 4 records that meet the join condition: product1, product2, product3 and product4.
A partial resultset for 4th record from d1 is:

| ID |  Product | ID |  Product |
|----|----------|----|----------|
|  1 | product4 |  1 | product1 |
|  1 | product4 |  1 | product2 |
|  1 | product4 |  1 | product3 |
|  1 | product4 |  1 | product4 |

---

And the whole resultset of the query is:

| ID |  Product | ID |  Product |
|----|----------|----|----------|
|  1 | product1 |  1 | product1 |
|  1 | product2 |  1 | product1 |
|  1 | product2 |  1 | product2 |
|  1 | product3 |  1 | product1 |
|  1 | product3 |  1 | product2 |
|  1 | product3 |  1 | product3 |
|  1 | product4 |  1 | product1 |
|  1 | product4 |  1 | product2 |
|  1 | product4 |  1 | product3 |
|  1 | product4 |  1 | product4 |

---

Next lets apply GROUP BY clause to the above resultset:

select d1.id,d1.product,count(d1.product)
from dealer d1 
join dealer d2  
on d1.id=d2.id AND d1.product>=d2.product
group by d1.id,d1.product;

| ID |  Product | count(d1.product) |
|----|----------|-------------------|
|  1 | product1 |                 1 |
|  1 | product2 |                 2 |
|  1 | product3 |                 3 |
|  1 | product4 |                 4 |

---

And the last step is to apply HAVING count(..) <= 2 to filter out from the above resultset all records with count > 2

select d1.id,d1.product,count(d1.product)
from dealer d1 
join dealer d2  
on d1.id=d2.id AND d1.product>=d2.product
group by d1.id,d1.product
having count(d1.product)<=2;

| ID |  Product | count(d1.product) |
|----|----------|-------------------|
|  1 | product1 |                 1 |
|  1 | product2 |                 2 |

---

I hope that now it's should be clear how it works.
All the above steps are included in this demo: http://sqlfiddle.com/#!9/e4890/7