SQL LEFT JOIN  returns all rows from the left table even if there are no matches in the right table.

Syntax

SELECT * FROM table_name1 LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

MySQL Table – users

|+—-+——+
| id | name |
+—-+——+
|  1 | AAAA |
|  2 | BBBB |
|  3 | CCCC |
|  4 | DDDD |
|  5 | EEEE |
+—-+——+

MySQL Table – Orders

+—–+—–+——–+
| oid | uid | items  |
+—–+—–+——–+
|   1 |   1 | Pen    |
|   2 |   2 | Watch  |
|   3 |   3 | shoe   |
|   4 |   4 | mobile |
+—–+—–+——–+

Example

mysql> SELECT u.name,o.items FROM users as u LEFT JOIN orders as o ON u.id=o.uid ;
+——+——–+
| name | items  |
+——+——–+
| AAAA | Pen    |
| BBBB | Watch  |
| CCCC | shoe   |
| DDDD | mobile |
| EEEE | NULL   |
+——+——–+
5 rows in set (0.01 sec)

SQL INNER JOIN return rows when there is at least one match in both tables

Syntax

SELECT * FROM table_name1 INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

MySQL Table – users
|+—-+——+
| id | name |
+—-+——+
|  1 | AAAA |
|  2 | BBBB |
|  3 | CCCC |
|  4 | DDDD |
|  5 | EEEE |
+—-+——+

MySQL Table – Orders

+—–+—–+——–+
| oid | uid | items  |
+—–+—–+——–+
|   1 |   1 | Pen    |
|   2 |   2 | Watch  |
|   3 |   3 | shoe   |
|   4 |   4 | mobile |
+—–+—–+——–+

Example

mysql> SELECT u.name,o.items FROM users as u INNER JOIN orders as o ON u.id=o.uid ;
+——+——–+
| name | items  |
+——+——–+
| AAAA | Pen    |
| BBBB | Watch  |
| CCCC | shoe   |
| DDDD | mobile |
+——+——–+
4 rows in set (0.00 sec)

SQL JOIN can be used to fetch data from more than one tables based the relationship between columns in tables . Tables are usually related each with primary keys . primary key can be a column with unique value in it .

There are mainly 4 SQL JOINs

JOIN: Return rows when there is at least one match in both tables
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables

SQL AS  can be used to give alias name to a table or a column .

Syntax

For table

SELECT * FROM table_name AS alias_name

For column

SELECT column_name AS alias_name FROM table_name

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+

Example

mysql> SELECT * FROM users as lan;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+
4 rows in set (0.00 sec)

mysql> SELECT name as lan FROM users;
+——+
| lan  |
+——+
| PHP  |
| JS   |
| HTML |
| ASP  |
+——+
4 rows in set (0.00 sec)

SQL BETWEEN can be used to select data between two values given .

Syntax

SELECT * FROM table_name  WHERE column_name BETWEEN value1 AND value2

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+

Example

mysql> SELECT * FROM users WHERE id BETWEEN 2 AND 3;
+—-+——+
| id | name |
+—-+——+
|  2 | JS   |
|  3 | HTML |
+—-+——+
2 rows in set (0.00 sec)

SQL IN can be used to to have multiple values with WHERE clause .

Syntax

SELECT * FROM table_name WHERE column_name IN (val1,val2)

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+

Example

mysql> SELECT * FROM users WHERE name IN(‘PHP’,’JS’);
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
+—-+——+
2 rows in set (0.01 sec)

SQL wildcards can substitute for one or more characters when searching for data in a database.

It  uses with the SQL LIKE operator

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+

Example

mysql> select * from users WHERE name LIKE ‘%HP’;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
+—-+——+
1 row in set (0.00 sec)

Here % is the wildcard

Other wildcard available

‘ – ‘ – substitute for a single character
[char-list] – substitute for given characters

It searchs for a specified pattern in a column

SQL LIKE Syntax

SELECT * FROM table_name WHERE column_name LIKE pattern

The “%” sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

‘%ab ‘ – It will return data that end with the letter ‘ab’
‘ab%’ – It returns the data that starts with ‘ab’
‘ab’ – its similar to =
‘%ab%’ – it returns the data that has the value ‘ab’ in it

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+

Example

mysql> select * from users WHERE name LIKE ‘%HP’;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
+—-+——+
1 row in set (0.00 sec)

mysql> select * from users WHERE name LIKE ‘HT%’;
+—-+——+
| id | name |
+—-+——+
|  3 | HTML |
+—-+——+
1 row in set (0.00 sec)

mysql> select * from users WHERE name LIKE ‘HT%’;
+—-+——+
| id | name |
+—-+——+
|  3 | HTML |
+—-+——+
1 row in set (0.00 sec)

mysql> select * from users WHERE name LIKE ‘%p%’;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  5 | ASP  |
+—-+——+
2 rows in set (0.00 sec)

We can use this to specify the numbers of record sets to return

SQL LIMIT Syntax

SELECT * FROM table_name LIMIT some_value(should be a number)

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+

Example

mysql> select * from users  LIMIT 2;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
+—-+——+
2 rows in set (0.00 sec)

It delete rows in a table.

SQL DELETE Syntax

DELETE FROM table_name WHERE some_column=some_value

MySQL Table – users

+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  4 | PHP  |
|  5 | ASP  |
+—-+——+

Example

mysql> DELETE FROM users WHERE id = 4;
Query OK, 1 row affected (0.00 sec)

mysql> select * from users  ;
+—-+——+
| id | name |
+—-+——+
|  1 | PHP  |
|  2 | JS   |
|  3 | HTML |
|  5 | ASP  |
+—-+——+
4 rows in set (0.00 sec)