1.Difference between JOIN and UNION?
SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.
UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.
2.WHERE clause and HAVING clause?
WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.
To understand this, consider this example.
Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause
3.UNION, MINUS and INTERSECT?
UNION combines the results from 2 tables and eliminates duplicate records from the result set.
MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.
INTERSECT operator returns us only the matching or common rows between 2 result sets.
To understand these operators, let's see some examples. We will use two different queries to extract data from our emp table and then we will perform UNION, MINUS and INTERSECT operations on these two sets of data.
SELECT * FROM SYS.OBJECTS WHERE TYPE IN ('U')
SELECT * FROM SYS.columns
SELECT * FROM SYS.views WHERE TYPE IN ('V')
SELECT * FROM SYS.OBJECTS WHERE TYPE IN ('P')
SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. For example, if we have the department ID of each employee, then we can use this department ID of the employee table to join with the department ID of department table to lookup department names.
UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining. For example, if you have 2 employee tables with same structure, you can UNION them to create one result set that will contain all the employees from both of the tables.
2.WHERE clause and HAVING clause?
WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.
To understand this, consider this example.
Suppose we want to see only those departments where department ID is greater than 3. There is no aggregation operation and the condition needs to be applied on a static field. We will use WHERE clause
3.UNION, MINUS and INTERSECT?
UNION combines the results from 2 tables and eliminates duplicate records from the result set.
MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.
INTERSECT operator returns us only the matching or common rows between 2 result sets.
To understand these operators, let's see some examples. We will use two different queries to extract data from our emp table and then we will perform UNION, MINUS and INTERSECT operations on these two sets of data.
UNION
SELECT * FROM EMPLOYEEMASTER WHERE ID = 1
UNION
SELECT * FROM EMPLOYEEMASTER WHERE ID = 2
| ID | CompID | NAME | SAL | ||
|---|---|---|---|---|---|
| 1 | 2 | Gautam | 30000 | ||
| 2 | 2 | Puja | 20000 |
MINUS
SELECT * FROM EMPLOYEEMASTER
MINUS
SELECT * FROM EMPLOYEEMASTER WHERE ID > 2
| ID | CompID | NAME | SAL | ||
|---|---|---|---|---|---|
| 1 | Gautam | 30000 | |||
| 2 | 1 | Puja | 20000 |
INTERSECT
SELECT * FROM EMPLOYEEMASTER WHERE ID IN (2, 3, 5)
INTERSECT
SELECT * FROM EMPLOYEEMASTER WHERE ID IN (1, 2, 4, 5)
| ID | CompID | NAME | SAL | ||
|---|---|---|---|---|---|
| 5 | 2 | Gautam | 30000 | ||
| 2 | 1 | Puja | 20000 |
4.Generate row number in SQL Without ROWNUMSELECT name, sal, (SELECT COUNT(*) FROM EMPLOYEEMASTER i WHERE o.name >= i.name) row_num
FROM EMPLOYEEMASTER o
order by row_num
| NAME | SAL | ROW_NUM |
|---|---|---|
| Gautam | 30000 | 1 |
| Puja | 20000 | 2 |
| Kiran | 18000 | 3 |
SELECT * FROM SYS.OBJECTS WHERE TYPE IN ('U')
SELECT * FROM SYS.columns
SELECT * FROM SYS.views WHERE TYPE IN ('V')
SELECT * FROM SYS.OBJECTS WHERE TYPE IN ('P')
