Introduction
In SQL, pseudo columns are special read-only columns that behave like regular columns but do not exist physically in a table. These columns provide useful metadata, such as row numbers, unique row identifiers, and system-generated values.
💡 Example: ROWNUM, ROWID, SYSDATE, and USER in Oracle SQL.
🔹 Why Are Pseudo Columns Important?
- Allow you to reference special values without storing them in tables.
- Improve query performance and database operations.
- Help in ranking, filtering, and debugging database records.
✅ In this complete guide, we will cover:
✔️ What are pseudo columns?
✔️ List of commonly used pseudo columns
✔️ Examples of using pseudo columns in SQL
✔️ Best practices for working with pseudo columns
Let’s dive in! 🚀
1. What Are Pseudo Columns?
Pseudo columns are special system-generated columns that appear in SQL queries but are not physically stored in a table. You can retrieve values from them but cannot insert, update, or delete their values.
Key Characteristics of Pseudo Columns:
✅ Read-Only: Cannot be modified.
✅ Generated by SQL Engine: Automatically assigned values.
✅ Useful for Metadata Retrieval: Row numbers, timestamps, user details, etc.
✅ Table-Specific: Available only in certain database systems (e.g., Oracle, PostgreSQL).
💡 Example: Oracle SQL provides ROWNUM, ROWID, and SYSDATE as pseudo columns.
2. Commonly Used Pseudo Columns in SQL
Here are some of the most popular pseudo columns used in different SQL databases:
A. Oracle SQL Pseudo Columns
| Pseudo Column | Description | Example Usage |
|---|---|---|
ROWNUM | Assigns a temporary row number to each record | SELECT ROWNUM, Name FROM Employees; |
ROWID | Returns the unique physical row address | SELECT ROWID FROM Employees; |
SYSDATE | Retrieves the current system date & time | SELECT SYSDATE FROM DUAL; |
USER | Retrieves the currently logged-in user | SELECT USER FROM DUAL; |
LEVEL | Used in hierarchical queries | SELECT LEVEL, Employee_Name FROM Employees CONNECT BY PRIOR Employee_ID = Manager_ID; |
B. PostgreSQL & MySQL Pseudo Columns
| Pseudo Column | Description |
|---|---|
SERIAL | Auto-incremented unique number for a column |
NOW() | Retrieves the current timestamp |
UUID() | Generates a unique identifier |
3. Practical Examples of Using Pseudo Columns
A. Using ROWNUM to Limit Results
sqlCopyEditSELECT ROWNUM, Name, Salary
FROM Employees
WHERE ROWNUM <= 5;
💡 Explanation: This retrieves the first 5 employees based on the default ordering.
B. Using ROWID to Identify Unique Rows
sqlCopyEditSELECT ROWID, Name FROM Employees;
💡 Explanation: Retrieves the physical row address of each record.
C. Using SYSDATE for Date Calculations
sqlCopyEditSELECT SYSDATE, SYSDATE + 30 AS NextMonth FROM DUAL;
💡 Explanation: Retrieves today’s date and calculates the date 30 days ahead.
D. Using USER to Get Logged-In User
sqlCopyEditSELECT USER FROM DUAL;
💡 Explanation: Shows the current database user executing the query.
4. Pseudo Columns in Hierarchical Queries (LEVEL)
In Oracle SQL, LEVEL is used for hierarchical queries to retrieve parent-child relationships.
Example: Displaying Employee Hierarchy
sqlCopyEditSELECT LEVEL, Employee_Name, Manager_ID
FROM Employees
CONNECT BY PRIOR Employee_ID = Manager_ID;
💡 Explanation: Retrieves nested hierarchy of employees based on reporting structure.
5. Best Practices for Using Pseudo Columns
✅ Use ROWNUM for Pagination: Quickly retrieve limited results.
✅ Leverage ROWID for Fast Row Access: Retrieve physical row location.
✅ Use SYSDATE for Time-Related Queries: Automatically fetch current timestamps.
✅ Apply USER for Auditing: Track database changes by user.
✅ Use LEVEL in Recursive Queries: Simplifies hierarchical data representation.
6. Differences Between ROWNUM and ROW_NUMBER()
| Feature | ROWNUM (Oracle) | ROW_NUMBER() (ANSI SQL) |
|---|---|---|
| Definition | Assigns temporary row numbers | Assigns unique row numbers within partitions |
| Order-Sensitive? | Yes, can change order results | No, maintains order defined by ORDER BY |
| Usage | Fast filtering of records | Ranking within partitions |
💡 Use ROW_NUMBER() if you need ranking within partitions and sorting control.
7. Deleting Duplicate Rows Using Pseudo Columns
To remove duplicate records from a table, we can use ROWID.
Example: Delete Duplicate Employees with the Same Name
sqlCopyEditDELETE FROM Employees
WHERE ROWID NOT IN (
SELECT MIN(ROWID) FROM Employees GROUP BY Name
);
💡 Explanation: Retains only the first occurrence of each employee and removes duplicates.
Conclusion
✅ Quick Recap of What We Learned:
✔️ Pseudo columns behave like real columns but are not stored in tables.
✔️ Popular pseudo columns include ROWNUM, ROWID, SYSDATE, and USER.
✔️ LEVEL is used for hierarchical queries.
✔️ ROWNUM helps in limiting results, while ROWID identifies unique rows.
✔️ Use SYSDATE for timestamp queries and USER for tracking active sessions.
🎯 Next Steps: Explore more SQL queries and best practices in our SQL Community Forum!
🔹 Further Reading: Check out Oracle’s Pseudo Column Documentation for more details.