This post will discuss different methods that can be used to compare two rows in Oracle:
- Selecting Rows Based on Specific Values
- Selecting Rows Depending on Multiple Conditions
- Selecting Rows Based on Inequality Operators
- Selecting Rows Based on a Comparison Operator
- Using the MINUS Operator
- Using the EXCEPT Operator
- Using the JOIN Operator
- Using the IN and NOT IN Operator
Method 1: Selecting Rows Based on Specific Values
To compare two rows in Oracle, the user can select the rows based on the specific value. For instance, compare the rows of a table named “PRODUCTS” depending upon the specified values of the “PRODUCT_ID” column:
WHERE PRODUCT_ID = 310 OR PRODUCT_ID = 311;
In the command above, only the rows will be returned where the “PRODUCT_ID” column value is “310” and “311”.
Output
The output depicted the result containing the rows based on specified values for easy comparison.
Method 2: Selecting Rows Depending on Multiple Conditions
The logical operators (“OR” and “AND”) can be utilized in the query to select the rows based on multiple conditions for comparison. Execute the command given below to extract a row based on multiple conditions:
WHERE (PRODUCT_ID = 310 OR PRODUCT_ID = 311)
AND STANDARD_COST = LIST_PRICE;
The command above compares two rows from the table “PRODUCTS” with “PRODUCT_ID” equals “310” or “311” that have the same standard cost and list price.
Output
The output displayed the rows extracted based on multiple conditions.
Method 3: Selecting Rows Based on Inequality Operators
To compare two rows with not equal values, utilize the “NOT EQUAL TO” (<>) operator in the WHERE clause. Run the command given below to compare the rows of a table named “PRODUCTS”:
WHERE (PRODUCT_ID = 310 OR PRODUCT_ID = 311)
AND STANDARD_COST LIST_PRICE;
The above command compares two rows that have “PRODUCT_ID” equal to “310” or “311” and have distinct standard costs and list prices.
Output
The output returned the result after comparing rows using the “NOT EQUAL TO” (<>) operator.
Method 4: Selecting Rows Based on a Comparison Operator
In Oracle, the comparison operators can be utilized to extract rows for comparison. For this post, let’s run the query given below to compare rows using comparison operators:
FROM PRODUCTS
WHERE PRODUCT_ID > 287
The above command selects the rows from the table “PRODUCTS” where the “PRODUCT_ID” values are greater than “287”.
Output
The output depicts the rows for comparison that are extracted using the comparison operator.
Method 5: Using the MINUS Operator
In Oracle, the MINUS operator can be utilized to compare two rows in Oracle. This operator compares the output of two “SELECT” statements and returns the rows that are present in the first “SELECT” statement:
FROM PRODUCTS
WHERE PRODUCT_ID > 287
MINUS
SELECT *
FROM PRODUCTS
WHERE PRODUCT_ID = 311;
The above command compares rows with the “PRODUCT_ID” greater than “287” but excludes rows where the “PRODUCT_ID” equals “311”.
Output
The output displayed the result using the MINUS operator for the comparison of rows.
Method 6: Using the EXCEPT Operator
In Oracle, the “EXCEPT” operator can also be utilized to compare two rows. This operator is like the MINUS operator, but it returns the non-redundant rows that are in present the first “SELECT” statement but not in the second:
FROM PRODUCTS
WHERE PRODUCT_ID > 287
EXCEPT
SELECT *
FROM PRODUCTS
WHERE PRODUCT_ID = 311;
The above command returns results to compare rows with “PRODUCT_ID” greater than “287” but exclude rows with “PRODUCT_ID” value equal to “311” and remove duplicates.
Output
The output depicted the rows for comparison using the “EXCEPT” operator.
Method 7: Using the JOIN Operator
The JOIN operator can be utilized to unify rows from two or more tables based on correlated columns. Let’s execute this command to use the JOIN operator on the “PRODUCTS” table for row comparison:
FROM PRODUCTS t1
JOIN PRODUCTS t2
ON t1.STANDARD_COST = t2.LIST_PRICE
WHERE t1.PRODUCT_ID > 288
AND t2.CATEGORY_ID >= 4;
The above command selects all columns and joins them with itself utilizing the “STANDARD_COST” and “LIST_PRICE” columns. Moreover, it returns results by selecting only rows where the “PRODUCT_ID” is greater than “288” and the “CATEGORY_ID” of “t2” is greater than or equal to “4”.
Output
The output depicted the result of row comparison using the JOIN operator.
Method 8: Using the IN and NOT IN Operator
In Oracle, the “IN” and “NOT IN” operators can be utilized for comparing rows by evaluating whether the values of multiple columns in one row match the values in another row or set of rows. To do so, run this command:
FROM PRODUCTS
WHERE (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID) IN (
SELECT PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID
FROM PRODUCTS
WHERE PRODUCT_ID > 280
)
AND (PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID) NOT IN (
SELECT PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID
FROM PRODUCTS
WHERE PRODUCT_ID > 288
);
The above command selects all columns from the table named “PRODUCTS” where “PRODUCT_ID” is greater than “280” for the combination of “PRODUCT_ID“, “PRODUCT_NAME“, and “CATEGORY_ID” columns. Additionally, it excludes rows where the “PRODUCT_ID” is greater than “288” for the same combination of columns.
Output
The output depicted the rows that fulfill the conditions in the query.
Conclusion
Oracle offers inequality, comparison, MINUS, EXCEPT, JOIN, IN, and NOT IN operators to compare two rows. Users effectively compare two rows in Oracle and extract the desired result from one or multiple tables based on specific values, multiple conditions, inequality, comparison, and logical operators. This post has demonstrated different methods to compare two rows in Oracle.
