This documentation is for the legacy Izenda 6 product. Documentation for the new Izenda 7 product can be found at https://www.izenda.com/docs/
Selects rows from two tables such that the value in one column of the first table also appears in a column of the second table.
SELECT DISTINCT [dbo].[Invoices].[CustomerName] AS 'Customer Name' FROM [dbo].[Orders] INNER JOIN [dbo].[Invoices] ON [dbo].[Invoices].[CustomerID]=[dbo].[Orders].[CustomerID];
A cross join will return a result table where each row from the first table is combined with each row from the second table.
SELECT DISTINCT [dbo].[Invoices].[CustomerName] AS 'Customer Name' FROM [dbo].[Orders] CROSS JOIN [dbo].[Invoices];
The Left Outer Join known also as Left Join returns all rows from the left table in the Left Outer Join clause, no matter if the joined columns match. A field in a result row will be null if the corresponding input table did not contain a matching row.
SELECT DISTINCT [dbo].[Invoices].[CustomerName] AS 'Customer Name' FROM [dbo].[Orders] LEFT OUTER JOIN [dbo].[Invoices] ON [dbo].[Invoices].[CustomerID]=[dbo].[Orders].[CustomerID];
The Right Outer Join known also as Right Join returns all rows from the right table in the Right Outer Join clause, no matter if the joined columns match. A field in a result row will be null if the corresponding input table did not contain a matching row.
SELECT DISTINCT [dbo].[Invoices].[CustomerName] AS 'Customer Name' FROM [dbo].[Orders] RIGHT OUTER JOIN [dbo].[Invoices] ON [dbo].[Invoices].[CustomerID]=[dbo].[Orders].[CustomerID];
The Full Outer Join known also as Full Join returns all rows from Both the Right Outer Join & Left Outer Join. A field in a result row will be null if the corresponding input table did not contain a matching row.
SELECT DISTINCT [dbo].[Invoices].[CustomerName] AS 'Customer Name' FROM [dbo].[Orders] FULL OUTER JOIN [dbo].[Invoices] ON [dbo].[Invoices].[CustomerID]=[dbo].[Orders].[CustomerID];
If you specify a filter for the column in first(left) table in JOIN clause, all filters will be applied as usual (i.e. all filters will be in the WHERE clause):
SELECT [dbo].[Order Details].[Discount], [dbo].[Orders].[OrderID] FROM [dbo].[Order Details] LEFT OUTER JOIN [dbo].[Orders] ON [dbo].[Orders].[OrderID]=[dbo].[Order Details].[OrderID] WHERE [dbo].[Order Details].[OrderID] = 10248
Now let's say we want to specify filter for the column in the second(right) table. In this case, we should add this filter to the JOIN clause itself:
SELECT [dbo].[Order Details].[Discount], [dbo].[Orders].[OrderID] FROM [dbo].[Order Details] LEFT OUTER JOIN [dbo].[Orders] ON [dbo].[Orders].[OrderID]=[dbo].[Order Details].[OrderID] AND [dbo].[Orders].[OrderID] = 10248
The reason why we shouldn't place the filter in the WHERE clause is that it will transform our LEFT OUTER JOIN to an INNER JOIN. For example, when you use "WHERE [dbo].[Order Details].[OrderID] = 10248", you remove all NULLs from the results (obviously NULL doesn't equal 10248). To avoid this (to retain NULLs in the results) we should follow ANSI-92 SQL syntax and use conditional joins.
We can also use an inner query to apply our outer left join:
SELECT [dbo].[Order Details].[Discount], Ord.[OrderID] FROM [dbo].[Order Details] LEFT OUTER JOIN (SELECT * FROM [dbo].[Orders] WHERE [dbo].[Orders].[OrderID] = 10248) AS Ord ON Ord.[OrderID]=[dbo].[Order Details].[OrderID]
When we apply filters within the JOIN clause, it's like we applied these filters separately to the table, and then added them to the JOIN. This can help speedup database performance.
Please refer to the articles below for more information:
Last edited by Joseph Adams, 2014-04-24 18:59:02