Difference between WHERE and HAVING clause | SQL

WHERE AND HAVING CLAUSE WITH EXAMPLE

This post explains the difference between WHERE and HAVING clauses and how to use them with example scenarios.

---

WHERE:

• WHERE clause is used to filter only the desired records in the table.
• Records that don't meet the condition are removed prior to the grouping operation.

HAVING:

• HAVING clause is used as a secondary filter. If the value is calculated in your query and the value is not readily available in your table then you need use the HAVING clause.
• This filter is applied after your aggregate functions are executed in order to meet the mentioned HAVING condition from your resultant.

For example, let's consider you have a data set with ticket sales details of a game by team.

Scenario 1 :

The resultant here will be each team’s sum of sales from every game that had sales greater than 50,000.

SELECT 
team, 
SUM(sales_amt ) 
FROM 
games 
WHERE 
sales_amt > 50000 
GROUP BY 
team;

Scenario 2 :

The resultant here will be only teams that have total sales of greater than 5,00,000.

SELECT 
team, 
SUM(sales_amt ) 
FROM 
games 
GROUP BY 
team
HAVING 
SUM(sales_amt ) > 500000;

Scenario 3 :

The resultant here will be only teams that have grossed more than 5,00,000 from games that did more than 50,000 in sales.

SELECT 
team, 
SUM(sales_amt ) 
FROM 
games 
WHERE 
sales_amt > 50000 
GROUP BY 
team
HAVING 
SUM(sales_amt ) > 500000;

WHERE VS HAVING:

Features	WHERE	HAVING
Syntax	WHERE condition;	GROUP BY columns HAVING condition;
Example	WHERE sales_amt > 50000;	GROUP BY team HAVING SUM(sales_amt ) > 500000;
What value can be used condition	Direct value from table	Derived  aggregate value from table
When to use WHERE/HAVING clause	To filter data based on values available in the table.	To filter data based on aggregate values derived from values available in the table

---

Read More:

• What does count 1 mean in SQL?

---

• How to include a single quote in a SQL query?

---

• Create a table from an existing table

---

• Snowflake NVL vs NVL2

---

Create a table from an existing table | SQL

CREATE A NEW TABLE FROM THE EXISTING TABLE  WITH DATA OR WITHOUT DATA ALONG WITH FEW COLUMNS OR ALL THE COLUMNS USING ONE TABLE OR MULTIPLE TABLES

These SQL queries will create the basic table and its fields but not indexes, constraints, or triggers as it is in the existing table.

---

CREATE AN EMPTY TABLE FROM AN EXISTING TABLE:

Syntax:

CREATE TABLE new_table 
  AS (SELECT * 
      FROM old_table WHERE 1=2); 

Example:

CREATE TABLE suppliers 
AS (SELECT * 
 FROM companies 
 WHERE 1=2);

---

CREATE A TABLE FROM AN EXISTING TABLE WITH DATA:

Syntax:

CREATE TABLE new_table 
  AS (SELECT * 
      FROM old_table); 

Example:

CREATE TABLE suppliers 
AS (SELECT * 
 FROM companies 
 WHERE id > 1000);

---

CREATE A TABLE WITH ONLY A FEW COLUMNS FROM AN EXISTING TABLE WITH DATA:

Syntax:

CREATE TABLE new_table 
AS (SELECT column_1, column2, ... column_n 
FROM old_table); 

Example:

CREATE TABLE suppliers 
AS (SELECT id, address, city, state, zip 
FROM companies 
WHERE id > 1000); 

---

CREATE A TABLE WITH ONLY A FEW COLUMNS FROM AN EXISTING MULTIPLE TABLES WITH DATA:

Syntax:

CREATE TABLE new_table 
  AS (SELECT column_1, column2, ... column_n 
      FROM old_table_1, old_table_2, ... old_table_n); 

Example:

CREATE TABLE suppliers 
AS (SELECT companies.id, companies.address, categories.cat_type 
FROM companies, categories 

WHERE companies.id = categories.id 
AND companies.id > 1000); 

---

Read More:

What does count 1 mean in SQL?

How to include a single quote in a SQL query?

---

Types Of Dimension Tables with examples | Datawarehousing

---

DIMENSION:

The dimension table contains dimensions of fact.

It contains keys and descriptive attributes for measurements in the fact table.

---

TYPES OF DIMENSION:

Conformed dimension:

Conformed dimensions mean the exact same thing with every possible fact table to which they are joined.

Example: 

The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.

Junk dimension:

A junk dimension is a collection of random transactional codes flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes.

Example: 

Assume that we have a gender dimension and marital status dimension. In the fact table, we need to maintain two keys referring to these dimensions. Instead of that create a junk dimension that has all the combinations of gender and marital status (cross join gender and marital status table and create a junk table). Now we can maintain only one key in the fact table.

Degenerate dimension:

A degenerate dimension is a dimension that is derived from the fact table and doesn’t have its own dimension table.

Example: 

A transactional code in a fact table.

Role-playing dimension:

Dimensions that are often used for multiple purposes within the same database are called role-playing dimensions. 

Example: 

A date dimension can be used for “date of sale”, as well as “date of delivery”, or “date of hire”.

SCD – Slowly Changing Dimension

TYPE – 1:  Updates changes without preserving history.

TYPE – 2:  Maintains history by storing the value as a new record.

• Effective date
• Flagging
• Versioning

TYPE – 3:  Maintains history as the current and previous 

version column-wise.

---

Read More :

• Optimization in Informatica | Interview Question
• Performance Tuning of Informatica mapping | Interview Question

---