PART 1: SQL VIEW
What is a SQL View?
A View in SQL is a virtual table.
It does NOT store data itself.
Instead, it stores a SQL query.
Think of it like:
A saved SELECT statement that you can treat like a table.
Why Use a View?
- Hide complex queries
- Simplify repeated queries
- Improve security (hide certain columns)
- Make reports easier
Example Table: Employees
Here is our sample table:
| id | name | department | salary | bonus | |----|---------|------------|--------|--------| | 1 | Alice | HR | 50000 | 5000 | | 2 | Bob | Sales | 60000 | NULL | | 3 | Charlie | IT | 55000 | 3000 | | 4 | David | HR | 45000 | NULL | | 5 | Eve | Sales | 70000 | 7000 |
Creating a View
Suppose we want a view that shows only HR employees.
CREATE VIEW HR_Employees AS SELECT id, name, salary FROM Employees WHERE department = 'HR';
Now we can use the view like a table:
SELECT * FROM HR_Employees;
Even though it looks like a table, it’s just a saved query.
Updating a View
You can replace a view like this:
CREATE OR REPLACE VIEW HR_Employees AS SELECT id, name, salary, bonus FROM Employees WHERE department = 'HR';
Dropping a View
DROP VIEW HR_Employees;
🔹 PART 2: SQL NULL
What is NULL?
NULL means:
"No value" or "Unknown value"
It is NOT:
- 0
- Empty string ''
- False
It means the value is missing.
Example from our table:
Bob’s bonus is NULL.
That means we don’t know his bonus.
Important Rule About NULL
You CANNOT compare NULL using =.
❌ Wrong:
SELECT * FROM Employees WHERE bonus = NULL;
✅ Correct:
SELECT * FROM Employees WHERE bonus IS NULL;
To check for NOT NULL:
SELECT * FROM Employees WHERE bonus IS NOT NULL;
🔹 PART 3: NULL Functions
NULL functions help you handle missing values.
1️⃣ IFNULL() (MySQL)
Replaces NULL with another value.
SELECT name, IFNULL(bonus, 0) AS bonus FROM Employees;
If bonus is NULL, it becomes 0.
2️⃣ COALESCE() (Works in Most Databases)
Returns the first non-null value.
SELECT name, COALESCE(bonus, 0) AS bonus FROM Employees;
You can even do:
SELECT name, COALESCE(bonus, salary, 0) FROM Employees;
It checks:
- bonus
- salary
- 0
It returns the first one that is NOT NULL.
3️⃣ NULLIF()
Returns NULL if two values are equal.
SELECT NULLIF(0, 0);
Result: NULL
Example:
SELECT name, NULLIF(bonus, 0) FROM Employees;
If bonus is 0, it becomes NULL.
🔹 PART 4: Real Example Using NULL in Calculations
Problem:
If we calculate:
SELECT name, salary + bonus AS total_income FROM Employees;
If bonus is NULL → result becomes NULL.
Because:
Anything + NULL = NULL
Solution Using COALESCE
SELECT name,
salary + COALESCE(bonus, 0) AS total_income
FROM Employees;
Now NULL bonus becomes 0.
🔹 PART 5: Summary Table (Markdown)
| Concept | Purpose | Example | |--------------|--------------------------------------|----------| | VIEW | Virtual table based on query | CREATE VIEW view_name AS SELECT... | | NULL | Missing/unknown value | bonus IS NULL | | IS NULL | Check for NULL values | WHERE bonus IS NULL | | IS NOT NULL | Check for non-NULL values | WHERE bonus IS NOT NULL | | IFNULL() | Replace NULL (MySQL) | IFNULL(bonus, 0) | | COALESCE() | First non-null value | COALESCE(bonus, 0) | | NULLIF() | Returns NULL if values are equal | NULLIF(bonus, 0) |
-- Create View
CREATE VIEW HR_Employees AS
SELECT id, name, salary
FROM Employees
WHERE department = 'HR';
-- Use View
SELECT * FROM HR_Employees;
-- Replace View
CREATE OR REPLACE VIEW HR_Employees AS
SELECT id, name, salary, bonus
FROM Employees
WHERE department = 'HR';
-- Drop View
DROP VIEW HR_Employees;
-- Check NULL
SELECT * FROM Employees
WHERE bonus IS NULL;
SELECT * FROM Employees
WHERE bonus IS NOT NULL;
-- IFNULL (MySQL)
SELECT name, IFNULL(bonus, 0) AS bonus
FROM Employees;
-- COALESCE
SELECT name, COALESCE(bonus, 0) AS bonus
FROM Employees;
SELECT name, COALESCE(bonus, salary, 0)
FROM Employees;
-- NULLIF
SELECT NULLIF(0, 0);
SELECT name, NULLIF(bonus, 0)
FROM Employees;
-- NULL in calculation (wrong way)
SELECT name, salary + bonus AS total_income
FROM Employees;
-- Correct way using COALESCE
SELECT name,
salary + COALESCE(bonus, 0) AS total_income
FROM Employees;