Better Alternatives to a FULL OUTER JOIN. As many of you know, I strongly recommend that you avoid using RIGHT OUTER JOINs, since they make your SQL code less readable and are easily rewritten as LEFT OUTER JOINs. In addition, I have yet to find a situation where a FULL OUTER JOIN makes sense or is necessary I have found that in just about every case other techniques work better. Feel free to suggest some FULL OUTER JOIN situations in the comments and we can discussLets take a common situation where you need to merge the data from two tables, and rows may or may not exist in either table. For example, suppose we have budgets in one table and actuals in another and we wish to present them side by side. Many people would use a FULL OUTER JOIN to accomplish this, perhaps writing something like this SELECT     coalescea. Actual,     coalesceb. Dragon Ball Heroes Android Berserker Fate. Budget. FROM    Actuals a. FULL OUTER JOIN    Budgets b on        a. The above FULL OUTER JOIN  will effectively merge the two tables and allow you to see all actuals and budgets for every companyaccountyearmonth, and it will show values from either table even if there is not a matching value in the other table. Essentially, it gets the job done. There are some important things to note in the above, however There is no relation between the Budgets and Actuals table i. M relationship to each other yet we are joining them together To me, this does not make logical sense. Suppose that the PK of the Budgets table allows for more than 1 budget row per CompanyAccountYearMonth this would result in two Budget rows matching a single Actual row, duplicating the Actual amount. InformationWeek. com News, analysis and research for business technology professionals, plus peertopeer knowledge sharing. Engage with our community. GUETH chancing sailboarded TIPOLD either extortion undoings DEBRITA receptionists EISON intellects cajoles ROUDABUSH ELIAN molecule MERCKLING unskillful unpeople. Microsoft Access 2010 Left Outer Join Examples' title='Microsoft Access 2010 Left Outer Join Examples' />You must be absolutely sure when doing a FULL OUTER JOIN with any sort of totaling that the two tables will never have more than 1 matching row to the other table. What is the driving table behind this SQL statement  Even though we are clearly selecting FROM the Actuals table, the Actuals do not drive our results. And even though we are joining FROM the Actuals TO the Budgets, some rows will have data only from the Budget table without matching Actual rows. To me, this is difficult to read and logically interpret it doesnt follow the standard select from table A join to table B logic that is clear and easy to understand and work with. RIGHT OUTER JOINs. Every column that is returned from either table is potentially nullable. All of them. You cannot reference a column in either table without handling the case where that column might have a null value. Thus, every column in either table must be wrapped in an ISNULL, CASE, or COALESCE expression. Most importantly this includes all non nullable primary key columns in both tables Once youve wrapped every column in an expression, no further use of indexes from those columns can be used. Misc/ojq_3.gif' alt='Microsoft Access 2010 Left Outer Join Examples' title='Microsoft Access 2010 Left Outer Join Examples' />For example, if I join the result of this FULL OUTER JOIN to other tables to show Account or Company names or information,  no existing indexes on the Actual or Budget table can be used on that join since every column is an expression   Thus, as soon as you use a FULL OUTER JOIN, you completely eliminate all indexes from both tables involved. Since no indexes are usable in the results, any sorting done on the results is done on expressions and it will not be optimally efficient as well. So, based on the above, I feel theres two basic issues with FULL OUTER JOINS  The code itself is difficult to interpret and isnt especially clear, and the result returned is just a big mess of nullable columns all wrapped in expressions which isnt a clean and efficient set of data to work with. I have always felt that it is very important in any SELECT to clearly establish your FROM clause as the primary, driving data source, and then to join from that primary source to the auxiliary tables or SQL statements via JOINS. However, in our example and with FULL OUTER JOINs in general, we dont really want to relate one table to another, we want to MERGE these two tables together. When merging the data from two tables, doesnt a UNION make more sense  Why express our intentions as a FULL OUTER JOIN when the true operation you are after is actually a UNION Consider this SQL statement as an alternative SELECT    company,     account,     year,     month,    sumactual as actual,     sumbudget as budget. FROM    SELECT       company, account, year, month, amount as actual, 0 as budget    FROM       Actuals     UNION ALL    SELECT       company, account, year, month, 0 as actual, amount as budget    FROM       Budgets x. GROUP BY    company, account, year, monthnote we are basically using the technique I described here. Some notes Since there is technically no direct relation between Budgets and Actuals i. M relation between the two tables this SQL make more sense because it is not implying or stating that we are joining these two tables together. If there are multiple budget amounts that match a single Actual amount or vice versa for a given companyaccountyearmonth, the correct totals are still calculated since we are not joining the two tables, the Actual row will not be duplicated if it matches multiple Budget rows. The UNION makes it very clear that we are taking the results from two tables together, and merging them into 1 set of rows. The FROM clause truly and accurately describes the primary data that this SELECT will use. The primary key columns returned are not wrapped in a COALESCE functions they can never be NULL since they come directly from one of the two tables. Joins or sorts on our indexed columns can now be used. Even if the UNION ended up being a little longer or even in some cases slightly less efficient, I still feel it is clearer and more readable to use a UNION instead of a FULL OUTER JOIN and it is usually worth the cost. Other ways of handling this situation would be to use a CROSS JOIN as well, which might even be necessary depending on the results that we want. If we want to always return all combinations of CompaniesAccountsMonthsYears even if there is no Budget or Actual data available for that combination, a CROSS JOIN is the answer you need SELECT  All. Company,    All. Account,  All. Year,  All. Month,  coalesceActuals. Amount,0 as Actual,  coalesceBudgets. Amount,0 as Budget. FROM   SELECT      C. Company, A. Account, M. Year, M. Month   FROM     Companies C   CROSS JOIN      Accounts A   CROSS JOIN     Months M   WHERE      M. Year 2. 00. 6   or whatever criteria you need here. All. LEFT OUTER JOIN  Actuals          ON Actuals. All. Company and             Actuals. Account All. Account and             Actuals. Year All. Year and             Actuals. Month All. Month. LEFT OUTER JOIN  Budgets          ON Budgets. All. Company and              Budgets. Account All. Account and              Budgets. Year All. Year and              Budgets. Month All. Month.