In SQL, NON-EQUI JOIN is a type of join condition where a relationship between two tables is established using comparative operators other than the equal sign. Unlike the EQUI JOIN which uses the equal sign to match rows based on specified columns, NON-EQUI JOIN uses operators like greater than, less than, or not equal to instead of equals.

Advantages of NON-EQUI JOIN:

1. Flexible Criteria: NON-EQUI JOIN allows us to join tables based on flexible criteria, not limited to equality.

2. Versatility: It can be used to compare values using operators like greater than, less than, and not equal to, enabling a broader range of data comparisons.

Example:

Suppose we have two tables, employees and salaries, and we want to retrieve details of employees whose salaries are greater than the average salary in the salaries table. We can achieve this using NON-EQUI JOIN as shown in the SQL query below:

SELECT e.employee_id, e.employee_name, s.salary
FROM employees e
JOIN salaries s ON s.salary > (SELECT AVG(salary) FROM salaries)
WHERE e.employee_id = s.employee_id;

In this example, the NON-EQUI JOIN condition is ON s.salary > (SELECT AVG(salary) FROM salaries), which compares each salary in the employees table to the average salary in the salaries table.

Using NON-EQUI JOIN opens up a new dimension for querying and analyzing data, providing a powerful tool for complex data retrieval and analysis.

For more information, you can refer to the MySQL documentation.

Now, you can confidently use the NON-EQUI JOIN to unleash the full potential of SQL joins!