Under 2 min. | SQL | A ‘vs’ B Concepts
Delete vs Drop vs Truncate, Having vs GroupBy, Unionall vs Union and many more.
Where vs Having
Where works with individual row queries.
Having works when you have to perform calculations on multiple rows of a single column or with aggregate functions.
Union vs Union All
Union removes duplicate records whereas Union all doesn’t.
Union operator combines result sets of two or more select statements with conditions as -
- Same no. of columns in both the tables.
- Columns with similar data types.
- Columns must be in the same order.
Rank vs Dense Rank
In Dense Rank, you get continuous numbers but in rank, you get discontinuous numbers.
Delete vs Truncate vs Drop
- Truncate deletes all the records from an existing table but not the table itself. The structure or schema of the table is preserved.
- removes the records from a table without scanning it. This is why it is faster than the DELETE statement.
- TRUNCATE TABLE statement is a DDL command so it can not be rolled back.
- used to delete all or selective deletion using where clause.
- DELETE is a DML Command so it can be rolled back.
- used to delete existing database objects like databases, tables, views,
- DROP is a DDL Command. Objects deleted using DROP are permanently lost and it cannot be rolled back.
- removes the table definition and all the data, indexes, triggers, constraints and permission specifications for that table.
Primary key vs Unique key
There can be only one Primary key in a table and it can’t have null values.
Multiple unique keys can be there and null values are entertained.
IN vs EXISTS
IN is multiple OR whereas EXISTS returns a true or false value.
where xyz IN (a,b,c)
where EXISTS (select statement)
When to use/ some best practices —
- IN- Bigger Outer query and smaller inner query and vice-versa for EXISTS.
- IN- To compare one value to several values whereas EXISTS tells you whether a query returned you any results or not.
Order BY vs Group BY
Order BY is sorting either in ASC. or DESC. order whereas
Group BY is used with aggregated functions.
Group BY follows WHERE clause in select statements
Where cannot be used after group by, we use Having.
JOIN vs SUB-Query
In Sub-Query, we can only select/print from the first table and is slower as compared to JOIN.
JOIN vs UNION
Union combines rows and Join merges columns.
Union increase vertically, Join increase horizontally.