Skip to content

Latest commit

 

History

History
339 lines (264 loc) · 8.49 KB

Difference Between.md

File metadata and controls

339 lines (264 loc) · 8.49 KB

Difference Between

1. DELETE vs TRUNCATE
2. WHERE vs HAVING
3. UNION vs UNION All
4. PRIMARY KEY vs UNIQUE
5. PRIMARY KEY vs FOREIGN KEY
6. IN vs EXIST
7. ORDER BY vs GROUP BY
8. JOIN vs Sub Query
9. UNION vs JOIN
10. INDEX
11. Clustered Index vs Non Clustered Index
12. Stored Procedure vs Function
13. INNER JOIN vs OUTER JOIN
14. LEFT JOIN vs RIGHT JOIN

1. DELETE vs TRUNCATE vs DROP

DELETE TRUNCATE DROP
DML DDL DDL
Remove some or all rows Remove all rows Remove table from database
WHERE Clause No WHERE Clause No WHERE Clause
Roll Back No Roll Back No Roll Back
Doesn't removes permanently Remove records permanently Remove records, indexes, structures, metadata permanently

DELETE

DELETE FROM employee
WHERE employee_ID IN (1,2,3);

TRUNCATE

TRUNCATE TABLE employee;

DROP

DROP TABLE employee;

2. WHERE vs HAVING

WHERE HAVING
Aggregate funtions are not allowed Aggregate functions are allowed (SUM, AVG, MIN, MAX, COUNT)
Only supports filter conditions based on the existing columns Support filters based on aggregated results
  • HAVING clause is used only after GROUP BY clause.
  • Aggregate Functions are use to summarize data (Returns single aggregated value for multiple rows)

SUM | AVG | COUNT | MAX | MIN

WHERE

SELECT * FROM employee
WHERE designation = 'Data Scientist';

HAVING

SELECT MAX(salary)
FROM employee
GROUP BY designation
HAVING MAX(salary) > 100000;

3. UNION vs UNION ALL

Combines 2 or more tables

  • Tables must have same number of columns
  • Columns must have same data type
  • Columns must have same order of columns
UNION UNION ALL
Keep distinct records Keep duplicate records

UNION

SELECT * FROM sales
UNION
SELECT * FROM product
ORDER BY product_name;

UNION ALL

SELECT * FROM sales
UNION ALL
SELECT * FROM product
ORDER BY product_name;

4. PRIMARY KEY vs UNIQUE

Primary Key Unique Key
We can have only one PRIMARY KEY in a table We can have more than one UNIQUE KEY in a table
Do not accept NULL values Accepts only one NULL value.
Identify UNIQUE rows in the table Maintain UNIQUE data in a column.

PRIMARY KEY

CREATE TABLE person
(
    pID INT NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,    
    age INT
);

UNIQUE KEY

CREATE TABLE person
(
    pID INT NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,    
    age INT
);

5. PRIMARY KEY vs FOREIGN KEY

PRIMARY KEY FOREIGN KEY
Identify UNIQUE row in the table Column in a table which is PRIMARY KEY in another table
We can have only one PRIMARY KEY in the table We can have more than one FOREIGN KEY in the table
PRIMARY KEY can not accept NULL value FOREIGN KEY accepts NULL values
PRIMARY KEY will not allow Duplicate value FOREIGN KEY will allow duplicate value.

PRIMARY KEY

CREATE TABLE person
(
    pID INT NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,    
    age INT
);

Unique Key

CREATE TABLE person
(
    pID INT NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,    
    age INT
);

6. IN vs EXISTS

IN

  • Multiple OR
SELECT * FROM employee
WHERE city = 'Mumbai' OR city = 'Bangalore' OR city = 'Pune';
SELECT * FROM employee
WHERE city IN (`Mumbai`, `Bangalore`, `Pune`);
SELECT * FROM sales
WHERE city IN (SELECT city FROM returns)

EXISTS

  • Returns either True or False value (Tests for existance of records in a sub query)
SELECT * FROM sales
WHERE EXISTS (SELECT city FROM returns
              WHERE returns.ID = sales.ID AND price < 500)

7. ORDER BY vs GROUP BY

ORDER BY GROUP BY
Sorting in ASC or DESC order Used with Aggregate functions

ORDER BY

SELECT * FROM sales
ORDER BY city DESC;

GROUP BY

SELECT is_active, COUNT(*)
FROM customers
GROUP BY is_active;

WHERE Clause is used with SELECT Clause before GROUP BY

SELECT designation, salary 
FROM employee
WHERE designation IN ('Data Scientist', 'Data Analyst', 'Business Analyst', 'Data Architect', 'Machine Learning Engineer')
GROUP BY designation;

HAVING Clause is used with GROUP BY and WHERE Clause can't be used after GROUP BY

SELECT model, price 
FROM vehicles
GROUP BY model
HAVING SUM(price) > 5000000
ORDER BY price DESC;

8. JOIN vs Sub Query

Combine data from different tables into a single table

Sub Query

-- Select only from first table:
SELECT phone, customer_name
FROM customer
WHERE c_ID IN (SELECT c_ID 
               FROM orders)

JOIN

-- Select from either of the table:
SELECT phone, customer_name, order_ID
FROM customers c
JOIN orders o
ON o.customer_ID = c.customer_ID

9. UNION vs JOIN

UNION JOIN
Combine rows Merge columns
Number of columns and data type of columns should be same Combines on the basis of common column (ID/Values)
Vertical Horizontal

UNION

SELECT city from table1
UNION
SELECT city from table2;

JOIN

SELECT a.city, b.name
FROM departments a
JOIN employee b
ON a.ID = b.ID;

10. Index

  • Index are used for fast retrieval of data.
  • Index in the database is very similar to an Index in a book.
  • e.g. If you want to refer to a pages in a book, you first refer to the Index.

Types of Indexes

Single Column Index: Index based on only one column.

CREATE INDEX index_name 
ON table_name (column_name);

Unique Index: Does not allow any duplicate values to be inserted into the table.

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

Composite Indexes: An Index on two or more columns of a table.

CREATE INDEX index_name
ON table_name (column_name1, column_name2);

Implicit Indexes: Index automatically created by PRIMARY KEY constraint and UNIQUE constraint when an object is created.

11. Clustered Index vs Non Clustered Index

Clustered Index

  • Defines the order in which data is physically stored in a table.
  • When we create a table with ID as PRIMARY KEY, this automatically creates Clustered Index.

Non Clustered Index

  • Doesn't sort the physical data inside the table.
  • Non Clustered Index is stored at different place then the table data.
  • e.g. Textbook content | Index is located on first page but the actual content is all at different pages.
  • Index contains column value and address of the record.

12. Stored Procedure vs Function

Stored Procedure Function
Stored procedure can return 0 or N values Function must return a value
Can have multiple Input as well as Output parameters Can have only one Input parameter
Procedures cannot be called from function Functions can be called from procedure

13. Inner Join vs Outer Join

Inner Join Outer Join
Return only matching rows between both the tables Returns matching and unmatching rows between both the tables
Default Join It is not a Default Join

14. Left Join vs Right Join

Left Join Right Join
Return all the rows from the left table Return all the rows from the right table
Return only the matching rows from the right table Return only the matching rows from the left table