Limiting the result to first 5 records. Sorting total_amount in the revenue_per_customer (CTE result) in descending order. Select top 5 customers by revenue from the above CTE by Finally, selecting the customer_id, first_name, last_name and total_amount. Computing the total amount paid by customers for each rental transaction (as total_amount) grouping by customer_id. Joining the resultant table with payment table using rental_id. Joining the customer and rental tables using customer_id. Create a CTE named revenue_per_customer by In this example, we need to find out top and bottom 5 customers who generated the most revenue. Or SELECT * FROM left_table l INNER JOIN right_table r ON l.id = r.id SELECT * FROM left_table INNER JOIN right_table USING (id) In the below query result, we can see that only the records with common id in both left_table and right_table are returned. Inner join merges two tables by columns and returns only the matching records (based on the specified columns) in both the tables. We’ll now discuss a few important joins in SQL. When the names of the key columns are not same in both the tables, we need to match them using the ON keyword as shown above. In the above syntax, t1 is an alias of table_1 and t2 is of table_2. SELECT * FROM table_1 t1 JOIN table_2 t2 ON t1.t1_id = t2.t2_id Otherwise, we need to explicitly mention the key columns of both the tables as shown below. We use the keyword USING only when the key column has the same name in both the tables. In the above syntax, table_1 and table_2 are the two tables with the key column (matching column in both the tables), id. SELECT * FROM table_1 JOIN table_2 USING (id) 
Below is the generic syntax of SQL joins. Joins merge two tables based on the specified columns (generally, the primary key of one table and a foreign key of the other). Multiple tables can be merged by columns in SQL using joins. In this article, we’ll discuss the operators/commands in SQL that enable use to merge tables by rows or columns. We may need to merge multiple tables by rows (records) or columns (fields) to get the desired result. In practice, it is very rare to have an SQL query involving a single table.
0 kommentar(er)
