How to get results in separate rows after joining two tables I have two tables say - Plans and Discounts. Below are the sample data in Plans table.



Below are the sample data in the Discounts table.



These two tables are related using PlanName column. I am looking for a query in the most optimized way to get a resultset in such a way that a discount for a particular plan should come below the corresponding plans. Sample data below.
What you need is not a join which combines tables horizontally, you need a union set operator to join them vertically.
In your images all tables are named 'table 1' so I changed one of them to 'table 2' to make the query valid.

SELECT PlanName AS ProductName, Amount, StartDate, EndDate
FROM Table1
UNION ALL
SELECT DiscountName, Amount, StartDate, EndDate
FROM Table2
ORDER BY ProductName;


HTH