SQL Reference

LEFT(string, length)

string: The original string from which the leftmost characters will be extracted.
length: The number of characters to extract from the left side of the original string.

Example

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Output

OrderID	CustomerName
1	David
2	Maria
3	NULL

Explanation

The LEFT JOIN keyword returns all records from the left table (Orders), and the matched records from the right table (Customers). If there is no match, the result is NULL on the right side. In this scenario, the third order has no corresponding CustomerID in the Customers table, hence the NULL in the output.

LEFT(string text, n int) RETURNS text

string text: This is the source string from which the left part needs to be extracted.
n int: This parameter refers to the count of characters to be extracted from the left end of the source string.

Example

SELECT *
FROM table1
LEFT JOIN table2 ON table1.id = table2.id;

Output

 id | name  | id | value
----+-------+----+-------
  1 |  test |  1 |   123
  2 |  test |  2 |   456
  3 |  test |    |
(3 rows)

Explanation

The LEFT JOIN returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL in the right side when there is no match.

LEFT( character_expression, integer_expression )

character_expression: This is an expression of character or binary data type. It can range from strings of characters to values of numeric and datetime data types. SQL Server uses this expression to determine how many characters it will return in its output starting from the left of the data string.
integer_expression: This is a positive integer that determines the number of characters that the function will return from the left of the character_expression. The integer expression must be of the type bigint or any type that can be implicitly converted to bigint.

Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

Output

|CustomerName           | OrderID       |
|-----------------------|---------------|
|Alfreds Futterkiste    |              1|
|Ana Trujillo Emparedados|           NULL|
|Antonio Moreno Taquería|              3|
|Around the Horn        |              4|
|B's Beverages         |             NULL|

Explanation

The LEFT JOIN keyword returns all records from the left table (Customers), and the matched records from the right table (Orders). The result is NULL from the right side if there is no match.

LEFT(string1, n)

string1: This is the string or text expression from which the before mentioned number of characters need to be extracted.
n: This parameter represents the number of characters to extract from the left of the string. The number is specified from the starting position of the leftmost character in the string.

Example

SELECT left('Hello World', 5) FROM dual;

Output

Hell

Explanation

The LEFT function in Oracle is used to extract a specified number of characters from the left of a string. In this example, it extracts the first 5 characters from the string ‘Hello World’, returning ‘Hello’.

LEFT(string, number_of_chars)

string: This refers to the source string from which characters will be extracted.
number_of_chars: This indicates the number of characters to be extracted from the left side of the source string.

Example

CREATE TABLE Customers (
    ID int,
    Name text
);

INSERT INTO Customers (ID, Name) VALUES (1, 'Joan');
INSERT INTO Customers (ID, Name) VALUES (2, 'Bob');

CREATE TABLE Orders (
    ID int,
    CustomerID int,
    Product text
);

INSERT INTO Orders (ID, CustomerID, Product) VALUES (1, 1, 'Apples');
INSERT INTO Orders (ID, CustomerID, Product) VALUES (2, 2, 'Bananas');

SELECT Customers.Name, Orders.Product
FROM Customers
LEFT JOIN Orders
ON Customers.ID = Orders.CustomerID;

Output

| Name   | Product |
|--------|---------|
| Joan   | Apples  |
| Bob    | Bananas |

Explanation

In the given example, a LEFT JOIN operation is performed on two tables- ‘Customers’ and ‘Orders’. The JOIN is implemented based on the matching ‘ID’ column from the ‘Customers’ table and ‘CustomerID’ column from the ‘Orders’ table. The LEFT JOIN returns all the records from the left table (Customers), and the matched records from the right table (Orders). If no match is found, the result is NULL on the right side.