ROWNUM vs LIMIT in SQL

ROWNUM and LIMIT are both mechanisms used in different database management systems to restrict the number of rows returned by a query, but they are used in different contexts and have some differences:

1. ROWNUM:

   • ROWNUM is a pseudo-column in Oracle databases that returns a unique sequential number for each row retrieved by a query.
   • It is typically used in conjunction with a WHERE clause to limit the number of rows returned.
   • For example:

     SELECT * FROM table_name WHERE ROWNUM <= 10;
     

   • This query will return the first 10 rows from the table table_name.
   • 
     

2. LIMIT:

   • LIMIT is a clause used in SQL (Structured Query Language) to restrict the number of rows returned by a query in many relational database management systems such as MySQL, PostgreSQL, SQLite, etc.
   • It is typically used at the end of a SELECT statement to specify the maximum number of rows to return.
   • For example:

     SELECT * FROM table_name LIMIT 10;
     

   • This query will return the first 10 rows from the table table_name.

In summary, while both ROWNUM and LIMIT serve a similar purpose of restricting the number of rows returned by a query, ROWNUM is specific to Oracle databases, whereas LIMIT is more widely used across different database management systems. Additionally, the syntax and usage may differ slightly between the two.