SQL JOIN.

JOINS

SQL JOIN statement is used to combine rows of data from two or more tables based on a common column(field) between them.

This is to show the tables in the database in Microsoft SQL

use DWDiagnostics
SELECT table_name =name
  FROM sys.tables;

INNER JOIN.

This is the most fundamental SQL join. It allows us to merge two tables together.JOIN and INNER JOIN will return the same result.
INNER is the default join type for JOIN, so when you write JOIN the parser writes INNER JOIN
syntax

SELECT column name(s) -- columns you want to get from joined table,
      FROM table 1
      INNER JOIN Table2 
      ON table1.common_key_1 = table2.common_key_2;

Above are two tables of orders and customers imagine u want to find the phone numbers of customers who have ordered a laptop
SQL INNER JOIN statement returns all the rows from multiple tables as long as the conditions are met.

  SELECT*
  FROM employee_demographics  AS dem
  INNER JOIN employee_salary AS sal
  ON dem.employee_id =sal.employee_id
;

The On is used to show the columns we are merging together remember to name the two tables before the columns u are merging. We’d like to show, for each product, the associated Supplier. Show the ProductID, ProductName, and the CompanyName of the Supplier. Sort by ProductID.
This question will introduce what may be a new concept, the Join clause in SQL. The Join clause is used to join two or more relational database tables together in a logical way.

Select
  ProductID,ProductName 
  Supplier = CompanyName
From Products
 Join Suppliers
 on Products.SupplierID = Suppliers.SupplierID

To join the two tables,

SELECT EmployeeID,FirstName,LastName,Age,Salary,gender,DepartmentID,DeptDiv,
location,DeptSection,d.DepartmentID
FROM dbo.Employee.e 
JOIN dbo.Department d
ON e.DepartmentID =d.DepartmentID;

LEFT JOIN SQL .

SQL left JOIN statement returns all the rows from the left table and matching rows from the right table.
A LEFT JOIN returns all the rows from the left table and the matching rows from the right table. If there is no match in the right table, the result will contain NULL values for columns from the right table.
Result Set: It includes all rows from the left table, regardless of whether there is a match in the right table or not.
Non-Matching Rows: If there is no match in the right table, the columns from the right table will contain NULL values.

SELECT Employees.name, Salaries.salary
FROM Employees
LEFT JOIN Salaries
ON Employees.id = Salaries.emp_id;

RIGHT JOIN.

Also known as right outer join - a type of join that returns all the rows from the right table and the matching rows from the left table.If no matches are found NULL values are returned for the left tables.

SELECT column_names
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Full Join.

It combines the results of both LEFT JOIN and RIGHT JOIN. It returns all rows from both tables. If there is a match between the two tables the joined result will have both sides. Missing data will have NULL values.

SELECT column_names
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

CROSS JOIN.

Returns the Cartesian product of the two tables. It combines every row from the first table with every row from the second table.

SELECT columns
FROM table1
CROSS JOIN table2;

Subquery - is a select query that is enclosed inside another query. The inner select query is usually used to determine the results of the outer select query.

 Select Dept from employees 
 where salary =(Select Max(Salary) from Employees);

so Select Max(salary )from employees - is the inner query which is executed first then the outer query will be executed next which is select dept from employees.

UNION

1.UNION requires the subqueries to have the same number of columns and the same data types for the columns.If they do not,the query will fail to run.
2.Union technically may not return all the rows from its sub-queries.

Union by default removes all duplicate rows in the output.

COMMON TABLE EXPRESSIONS (CTE)
CTEs are simply a different version of subqueries.CTEs establish temporary tables by using the WITH clause.
The one advantage of CTEs is that they can be designed to be recursive. Recursive CTEs can reference themselves. Because of this feature, you can use them to solve problems that other queries cannot.

The CASE WHEN Function.
CASE WHEN is a function that allows a query to map various values in a column to other values.The g

1.What is the difference between Inner and self join?
A Self-join is a type of Inner join.
Inner join is used to return the records which are present in both tables. Whereas, in self-join, a table is joined to itself.

2.What distinguishes a full join from a cross join ?
A left Outer Join and a Right Outer join combined form a full outer Join. When the ON condition is not met, it inserts NULL values and returns all rows from both tables which match the query's WHERE clause. While a cross-join returns every possible combination of all rows by creating a cartesian product between both the two tables.

3.Describe the Equi Join.
In this kind of join, tables are combined based on model can effectively in the designated columns. Some equi join features are:

• The column names do not have to match.
• There are occasionally duplicate columns in the resulting table.
• On two tables, an equi join can be executed.

4.Can you describe the SQL nested join?
A nested join essentially uses one having joined table as an external input table and the other as an inner input table. A Nested loop join involves retrieving one row from the outer table searching for it in the inner table and repeating this process until all of the production rows from the outer table have indeed been found.

5.What is Natural Join?
A natural join establishes an implicit join clause based on the shared attributes of the two tables. The name of a shared attribute is the same across both tables. A comparison operator is not required for a natural join, in contrast to an equi join.

6.What do Fields and Tables do?
In a relational database, a table is a group of data elements arranged in rows and columns. A table can be used to represent relationships in a useful way. Tables are the most fundamental type of data storage.

7.SET@id =6; is used to define a SQL variable to put a value in a Variable.

8.How many primary keys can a table have ? - 1

9.NVarchar used to store JSON objects?

10.COUNT(*) function counts rows in a SQL query.

11.