Examples of Joining the First Row in SQL
1. Using Subquery With LIMIT
Using this approach you can limit how many rows you want to fetch from tables.
Syntax:
SELECT *
FROM table1 t1
JOIN (
SELECT *
FROM table2
ORDER BY column_name
LIMIT num_of_rows
) AS t2 ON t1.common_column = t2.common_column;
Here,
- JOIN: Combines rows from two or more tables based on a related column between them.
- AS: Renames a column or table with an alias to improve readability.
- SELECT: Retrieves data from one or more tables or expressions.
- ORDER BY: Sorts the result set based on specified columns or expressions.
- LIMIT: Constrains the number of rows returned in the result set.
Example: Querying Customer Details with Earliest Order Date
Creating Customer table:
CREATE table customers(customer_id int, customer_name varchar2, email varchar2);
-- Insert values into the customers table
INSERT INTO customers VALUES (1, 'John Doe', 'john.doe@example.com');
INSERT INTO customers VALUES (2, 'Jane Smith', 'jane.smith@example.com');
Creating Orders table:
CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT, order_date DATE );
-- Insert values into the orders table
INSERT INTO orders VALUES (101, 1, '2024-01-15');
In this example, we are fetching the details of customer from the table customer and order. In this example, we use nested query and LIMIT which constraints the number of rows returned in the list.
SELECT *
FROM customers c
JOIN (
SELECT *
FROM orders
ORDER BY order_date ASC
LIMIT 1
) AS o ON c.customer_id = o.customer_id;
Output:
In this output, we fetch the details of one customer from the table customer and order. In output, it returns the column customer_id, customer_name, email, order_id, order_date.
2. Using Subquery With TOP
In this approach, we fetch only top most row from the corresponding table.
Syntax:
SELECT *
FROM table1 t1
JOIN (
SELECT TOP 1 *
FROM table2
ORDER BY column_name
) AS t2 ON t1.common_column = t2.common_column;
Here,
- SELECT: Specifies columns to retrieve data from in tables or subqueries.
- FROM: Identifies the table(s) from which data will be retrieved.
- JOIN: Combines rows from different tables based on a related column.
- TOP 1: Selects only the first row from a result set based on specified criteria.
- ORDER BY: Arrange rows in a specified order, typically ascending or descending.
- AS: Provides an alias for a table or column to simplify referencing in the query.
- ON: Defines the condition for joining tables based on matching column values.
Example: Fetching Earliest Order for Each Customer
In this example, we are fetching the details of customer from the table customer and order. In this example, we use nested query and TOP which Selects only the first row from a result set based on specified criteria.
SELECT *
FROM customer c
JOIN (
SELECT TOP 1 *
FROM order
ORDER BY order_date ASC
) AS o ON c.customer_id = o.customer_id;
Output:
In this output, we fetch the details of one customer from the table customer and order. In output, it returns the column customer_id, customer_name, email, order_id, order_date.
3. Using Subquery With FETCH FIRST
Using this approach we fetch first number of rows of corresponding tables.
Syntax:
SELECT *
FROM table1 t1
JOIN (
SELECT *
FROM table2
ORDER BY column_name
FETCH FIRST 1 ROW ONLY
) AS t2 ON t1.common_column = t2.common_column;
Here,
- SELECT: Retrieves all columns from specified tables or subqueries.
- FROM: Specifies the source table(s) or subquery from which data will be retrieved.
- JOIN: Combines rows from different tables based on a related column or condition.
- FETCH FIRST 1 ROW ONLY: Limits the number of rows retrieved to only the first row, according to the specified ordering.
- ORDER BY: Specifies the column(s) by which the result set should be sorted, often in ascending or descending order.
- AS: Assigns an alias to a table or subquery for easier reference in the query.
- ON: Specifies the condition for joining tables, typically by matching values in specified columns.
Example: Retrieving Customer Details with Earliest Order Using FETCH FIRST
In this example, we are fetching the details of customer from the table customer and order. In this example, we use nested query and FETCH FIRST 1 ROW ONLY which Limits the number of rows retrieved to only the first row, according to the specified ordering..
SELECT *
FROM customer c
JOIN (
SELECT *
FROM order
ORDER BY order_date ASC
FETCH FIRST 1 ROW ONLY
) AS o ON c.customer_id = o.customer_id;
Output:
In this output, we fetch the details of one customer from the table customer and order. In output, it returns the column customer_id, customer_name, email, order_id, order_date.
How to Join First Row in SQL?
SQL(Structured Query Language) is a powerful tool that is used to manage and manipulate data in a database. Join is a very important tool in SQL that combines multiple tables based on related columns between them. Joining the first row of tables in SQL is a very useful technique when you need to fetch additional information from the tables. In this article, we see the different approaches with their syntax example and their explanation with output.
Contact Us