Query to fine "Order_num" that has no two equal "product_name"
Classic solution:
SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) &gt; 1;

Custom solutions:
SELECT DISTINCT t1.order_num
FROM a_test t1
JOIN a_test t2 ON t1.order_num = t2.order_num
WHERE t1.product_name != t2.product_name;

WITH cte AS ( SELECT order_num, 
                     DENSE_RANK() OVER (PARTITION BY order_num ORDER BY product_name) dr
              FROM a_test )
SELECT DISTINCT order_num
FROM cte
WHERE dr = 2;

...
fiddle


The one that you used 'Join' , again does not return the desired result. It returns those records that have equal order_num but their product_name are different and does not return records with same order_num that their product_name s are the same . Just this – Pantea


Do you mean that you need only orders which (1) have at least 2 products, and (2) have none product twice? If so then add .. AND COUNT(DISTINCT product_name) = COUNT(product_name) to the first query.

SELECT order_num
FROM a_test
GROUP BY order_num
HAVING COUNT(DISTINCT product_name) &gt; 1
AND COUNT(DISTINCT product_name) = COUNT(product_name);

fiddle