## Functional Dependency Special Cases 

ON cl.CountryCode = co.Code GROUP BY cl.CountryCode, cl.Language; 

Whereas an equality test in a WHERE  condition or INNER JOIN  condition is symmetric, an equality test in an outer join condition is not, because tables play different roles. 

Assume that referential integrity has been accidentally broken and there exists a row of countrylanguage  without a corresponding row in country . Consider the same query as in the previous example, but with a LEFT JOIN : 

SELECT co.Name, cl.Language, cl.Percentage * co.Population/100.0 AS SpokenBy FROM countrylanguage cl LEFT JOIN country co ON cl.CountryCode = co.Code GROUP BY cl.CountryCode, cl.Language; 

For a given value of cl.CountryCode , the value of co.Code  in the join result is either found in a matching row (determined by cl.CountryCode ) or is NULL -complemented if there is no match (also determined by cl.CountryCode ). In each case, this relationship applies: 

{cl.CountryCode} -> {co.Code} 

cl.CountryCode  is itself functionally dependent on { cl.CountryCode , cl.Language } which is a primary key. 

If in the join result co.Code  is NULL -complemented, co.Name  is as well. If co.Code  is not NULL - complemented, then because co.Code  is a primary key, it determines co.Name . Therefore, in all cases: 

{co.Code} -> {co.Name} 

Which yields: 

{cl.CountryCode, cl.Language} -> {cl.*,co.*} 

As a result, the query is valid. 

However, suppose that the tables are swapped, as in this query: 

SELECT co.Name, cl.Language, cl.Percentage * co.Population/100.0 AS SpokenBy FROM country co LEFT JOIN countrylanguage cl ON cl.CountryCode = co.Code GROUP BY cl.CountryCode, cl.Language; 

Now this relationship does not  apply: 

{cl.CountryCode, cl.Language} -> {cl.*,co.*} 

Indeed, all NULL -complemented rows made for cl  is put into a single group (they have both GROUP BY columns equal to NULL ), and inside this group the value of co.Name  can vary. The query is invalid and MySQL rejects it. 

Functional dependence in outer joins is thus linked to whether determinant columns belong to the left or right side of the LEFT JOIN . Determination of functional dependence becomes more complex if there are nested outer joins or the join condition does not consist entirely of equality comparisons. 

## Functional Dependencies and Views 

Suppose that a view on countries produces their code, their name in uppercase, and how many different official languages they have: 

