Under 2 min. | SQL | A ‘vs’ B Concepts

Picture Credit : Interviewbit

Where vs Having

Where works with individual row queries.

Union vs Union All

Union removes duplicate records whereas Union all doesn’t.

  • 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

  • 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,
    triggers, etc.
  • 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.

  • 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.

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pranav Bansal

Pranav Bansal

Highly curious generalist with a bag full of ideas!