Difference between Inner join and Left join

SQL Join is used for combining the rows between two or more tables using common fields/columns between these tables in a relational database. The goal of joining table is to extract the meaningful list of data. Joins will be obtained from the term called predicate. “Join” is a term used to combine fields of two or more tables by using common values between the tables.

There are different types of joins in SQL and they are –

• INNER JOIN
• CROSS JOIN
• SELF JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

Difference between Inner Join vs Left join

For demonstration purpose let’s create tables and let’s call it as – “tClass” and “tStudent”

tClass table structure

tStudent table structure

Inner join

Inner join matches the common records in two tables joined. Here in the above example we have “ClassID” in tclass table and “ClassID” in tstudent table. Below query is used to fetch the list of students which are in Class “10th”

SELECT s.StudentID, s.StudentName
FROM tClass c 

INNER JOIN tstudent s ON c.ClassID = s.ClassID

WHERE c.ClassID = 10

So above query is used to fetch the list of students which are studying in Class – “10th”. So here in “tstudent” table all students are stored irrespective of their classes and similarly in “tclass” table all classes are stored. So as the result of the above query, matched records between tstudent and tclass tables are searched and displayed. In the introduction part there was an introduction to the term called “predicate”, in the above query it is – “ON c.ClassID = s.ClassID”, which is one of the essential part of the join.

Note : There are multiple keys associated to each table like in tStudent table have “StudentID” as a primary key field and “ClassID” as a foreign key which inturn refer to the table – “tClass”.

• In Inner join each record of table A Is matched with each record of Table B and the matched records are then be displayed in the resultant table.
• JOIN word can be used instead of INNER JOIN, both meant the same.
• INNER JOIN is the intersection of data between table A and table B.

The above query can be rewritten without using inner join like below but the performance will be impacted compared to inner join –

SELECT s.StudentID, s.StudentName
FROM tClass c , tstudent s

WHERE c.ClassID = s.ClassID AND c.ClassID = 10

Left Join

• Left join will return all the rows from the left table and matching rows from right table.
• In case of no match with right side table it will return NULL value.
• “Left Join” and “Left Outer Join” are used interchangeably because records which are returned will be the same with either of these.

SQL Mathematical Operators

SQL Calculation fields can be used to perform mathematical ( + , - , * , /) operation on retrieved data. SQL Query with different mathematical operations performed on the table sales is given below

Table Name : Sales

Item	Quantity	Price	Reduction	Total
Item1	5	400	10	2000
Item2	6	300	10	1800
Item3	3	15	5	45
Item4	2	30	5	60
Item5	2	300	5	600
Item6	6	400	10	2400

For multiplication , following SQL Query is used . For example, Query to find the total of each based on the quantity and base price

select items, quantity* price Total from sales

Item	Total
Item1	2000
Item2	1800
Item3	45
Item4	60
Item5	600
Item6	2400

SQL Like and Distinct Command

SQL Like Query

For wildcard filtering “Like ” operator is used. Within a search string, % means match any number of occurrences of any character. For example, to fetch all the employees starting with letter "J" from employee table given below, following SQL query is used.

Table Name: Employee

Employee_ID	Employee_Name	Age
1	John	28
2	Alex	24
3	James	35
4	Roy	22
5	Kay	44

select * from employee where employee_name like 'J%'

The result of the above given query is given below.

mployee_ID	Employee_Name	Age
1	John	28
3	James	35

To fetch all names containing letter a, following SQL query is used. This query will fetch all records having letter 'a' at any position. Please note that, like query is case sensitive.

select * from employee where employee_name like '%a%'

Employee_ID	Employee_Name	Age
3	James	35
5	Kay	44

To fetch all names ending with letter y, following SQL query is used.

select * from employee where employee_name like '%y'

Employee_ID	Employee_Name	Age
4	Roy	22
5	Kay	44

Points to be noted before using Like Operator

SQL 'Like' search takes more time to process. So before using like operator following tips should be considered.

• Don't overuse wildcards. If another search operator will do, use it instead.
• When you do use wildcards, try to not use them at the beginning of the search pattern unless absolutely necessary. Search patterns that begin with wildcards are the slowest to process.
• Pay careful attention to the placement of the wildcard symbols. If they are misplaced, you might not return the data you intended.

Introduction to SQL

1.	You can add a row using SQL in a database with which of the following?
	A. ADD B. CREATE C. INSERT D. MAKE

2.	The command to remove rows from a table 'CUSTOMER' is:
	A. REMOVE FROM CUSTOMER ... B. DROP FROM CUSTOMER ... C. DELETE FROM CUSTOMER WHERE ... D. UPDATE FROM CUSTOMER ...

3.	The SQL WHERE clause:
	A. limits the column data that are returned. B. limits the row data are returned. C. Both A and B are correct. D. Neither A nor B are correct.

4.	Which of the following is the original purpose of SQL?
	A. To specify the syntax and semantics of SQL data definition language B. To specify the syntax and semantics of SQL manipulation language C. To define the data structures D. All of the above.

5.	The wildcard in a WHERE clause is useful when?
	A. An exact match is necessary in a SELECT statement. B. An exact match is not possible in a SELECT statement. C. An exact match is necessary in a CREATE statement. D. An exact match is not possible in a CREATE statement.