SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
SQL LEFT JOIN Syntax
| SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.
SQL LEFT JOIN Example
The "Persons" table:
| P_Id | LastName | FirstName | Address | City |
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
The "Orders" table:
| O_Id | OrderNo | P_Id |
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:
| SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
The result-set will look like this:
| LastName | FirstName | OrderNo |
| Hansen | Ola | 22456 |
| Hansen | Ola | 24562 |
| Pettersen | Kari | 77895 |
| Pettersen | Kari | 44678 |
| Svendson | Tove | |
The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no matches in the right table (Orders).
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
SQL RIGHT JOIN Syntax
| SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.
SQL RIGHT JOIN Example
The "Persons" table:
| P_Id | LastName | FirstName | Address | City |
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
The "Orders" table:
| O_Id | OrderNo | P_Id |
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
Now we want to list all the orders with containing persons - if any, from the tables above.
We use the following SELECT statement:
| SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
The result-set will look like this:
| LastName | FirstName | OrderNo |
| Hansen | Ola | 22456 |
| Hansen | Ola | 24562 |
| Pettersen | Kari | 77895 |
| Pettersen | Kari | 44678 |
| | | 34764 |
The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).
SQL FULL JOIN Keyword
The FULL JOIN keyword return rows when there is a match in one of the tables.
SQL FULL JOIN Syntax
| SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name |
SQL FULL JOIN Example
The "Persons" table:
| P_Id | LastName | FirstName | Address | City |
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
The "Orders" table:
| O_Id | OrderNo | P_Id |
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 15 |
Now we want to list all the persons and their orders, and all the orders with their persons.
We use the following SELECT statement:
| SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName |
The result-set will look like this:
| LastName | FirstName | OrderNo |
| Hansen | Ola | 22456 |
| Hansen | Ola | 24562 |
| Pettersen | Kari | 77895 |
| Pettersen | Kari | 44678 |
| Svendson | Tove | |
| | | 34764 |
The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as well.
he ROUND() Function
The ROUND() function is used to round a numeric field to the number of decimals specified.
SQL ROUND() Syntax
| SELECT ROUND(column_name,decimals) FROM table_name |
| Parameter | Description |
| column_name | Required. The field to round. |
| decimals | Required. Specifies the number of decimals to be returned. |
SQL ROUND() Example
We have the following "Products" table:
| Prod_Id | ProductName | Unit | UnitPrice |
| 1 | Jarlsberg | 1000 g | 10.45 |
| 2 | Mascarpone | 1000 g | 32.56 |
| 3 | Gorgonzola | 1000 g | 15.67 |
Now we want to display the product name and the price rounded to the nearest integer.
We use the following SELECT statement:
| SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products |
The result-set will look like this:
| ProductName | UnitPrice |
| Jarlsberg | 10 |
| Mascarpone | 33 |
| Gorgonzola | 16 |
The NOW() Function
The NOW() function returns the current system date and time.
SQL NOW() Syntax
| SELECT NOW() FROM table_name |
SQL NOW() Example
We have the following "Products" table:
| Prod_Id | ProductName | Unit | UnitPrice |
| 1 | Jarlsberg | 1000 g | 10.45 |
| 2 | Mascarpone | 1000 g | 32.56 |
| 3 | Gorgonzola | 1000 g | 15.67 |
Now we want to display the products and prices per today's date.
We use the following SELECT statement:
| SELECT ProductName, UnitPrice, Now() as PerDate FROM Products |
The result-set will look like this:
| ProductName | UnitPrice | PerDate |
| Jarlsberg | 10.45 | 10/7/2008 11:25:02 AM |
| Mascarpone | 32.56 | 10/7/2008 11:25:02 AM |
| Gorgonzola | 15.67 | 10/7/2008 11:25:02 AM |
The FORMAT() Function
The FORMAT() function is used to format how a field is to be displayed.
SQL FORMAT() Syntax
| SELECT FORMAT(column_name,format) FROM table_name |
| Parameter | Description |
| column_name | Required. The field to be formatted. |
| format | Required. Specifies the format. |
SQL FORMAT() Example
We have the following "Products" table:
| Prod_Id | ProductName | Unit | UnitPrice |
| 1 | Jarlsberg | 1000 g | 10.45 |
| 2 | Mascarpone | 1000 g | 32.56 |
| 3 | Gorgonzola | 1000 g | 15.67 |
Now we want to display the products and prices per today's date (with today's date displayed in the following format "YYYY-MM-DD").
We use the following SELECT statement:
| SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products |
The result-set will look like this:
| ProductName | UnitPrice | PerDate |
| Jarlsberg | 10.45 | 2008-10-07 |
| Mascarpone | 32.56 | 2008-10-07 |
| Gorgonzola | 15.67 | 2008-10-07 |
Meaning OF SQL
What is SQL?
- SQL stands for Structured Query Language
- SQL lets you access and manipulate databases
- SQL is an ANSI (American National Standards Institute) standard
What Can SQL do?
- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records in a database
- SQL can update records in a database
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures in a database
- SQL can create views in a database
- SQL can set permissions on tables, procedures, and views
SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.
The INSERT INTO Statement
The INSERT INTO statement is used to insert a new row in a table.SQL INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two forms.The first form doesn't specify the column names where the data will be inserted, only their values:
| INSERT INTO table_name VALUES (value1, value2, value3,...) |
| INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...) |
SQL INSERT INTO Example
We have the following "Persons" table:| P_Id | LastName | FirstName | Address | City |
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
We use the following SQL statement:
| INSERT INTO Persons VALUES (4,'Nilsen', 'Johan', 'Bakken 2', 'Stavanger') |
| P_Id | LastName | FirstName | Address | City |
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
| 4 | Nilsen | Johan | Bakken 2 | Stavanger |
Insert Data Only in Specified Columns
It is also possible to only add data in specific columns.The following SQL statement will add a new row, but only add data in the "P_Id", "LastName" and the "FirstName" columns:
| INSERT INTO Persons (P_Id, LastName, FirstName) VALUES (5, 'Tjessem', 'Jakob') |
| P_Id | LastName | FirstName | Address | City |
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
| 4 | Nilsen | Johan | Bakken 2 | Stavanger |
| 5 | Tjessem | Jakob | |