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)
      FROM table 1
      INNER JOIN Table2 
      ON table1.column_name = table2.column_name

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.

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.

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.