Which are the different types of joins?

By | September 18, 2017

INNER JOIN
Inner join shows matches only when they exist in both tables. Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers. Customerid and Orders. Customerid. So this SQL will only give you result with customers who have orders. If the customer does not have order, it will not display that record.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customer =Customer select [Medvisit].[dbo].[Visits].*,datename(month,[Medvisit].[dbo].[Patients].dteDate)as montha from [Medvisit].[dbo].[Visits] INNER JOIN [Medvisit].[dbo].[Patients] ON nPatient=nID

LEFT OUTER JOIN
Left join will display all records in left table of the SQL statement. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values. For example, you want to determine the amount ordered by each customer and you need to see who has not ordered anything as well. You can also see the LEFT OUTER JOIN as a mirror image of the RIGHT OUTER JOIN (Is covered in the next section) if you switch the side of each table.
SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON
Customer =Customer

RIGHT OUTER JOIN
Right join will display all records in right table of the SQL statement. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values. For example, you want to determine if there are any orders in the data with undefined Customer Id values (say, after a conversion or something like it). You can also see the RIGHT OUTER JOIN as a mirror image of the LEFT OUTER JOIN if you switch the side of each table.
SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON
Customer =Customer

Leave a Reply

Your email address will not be published. Required fields are marked *