Avidos Joins

Avidos Joins




​ Visual Explanation of Avido’s Joins                       




Avido uses only two of the many SQL joins available; the Inner Join and the Left Outer Join. It can be difficult to imagine how your data is affected when applying these joins – so below is some information to aid you in choosing the correct join which will reflect real world information. 

It is important to note the below information as joining tables incorrectly can distort data and result in an incorrect set of your data.

A good visual representation of SQL joins would be to use Venn diagrams. Let’s take our two sample tables, Table A and Table B:


Table A


Table B




ID

Name

1

Apple

2

Pie 

3

Cinnamon

4

Marshmallow




ID

Name

1

Popcorn

2

Apple

3

Biscuit

4

Cinnamon



Let's join these tables by the name field in a few different ways and display the results alongside a visual representation of those results in a Venn Diagram.



As you can see from above, only two of the Name fields match across both tables.











INNER JOIN

An Inner Join produces only the set of records that match in both Table A and Table B.

SQL SYNTAX:


AVIDO SYNTAX:


SELECT * FROM TableA A

INNER JOIN TableB B

ON A.name = B.name




RESULTS:




ID

Name

1

Apple

3

Cinnamon




ID

Name

2

Apple

4

Cinnamon







VENN DIAGRAM:













LEFT OUTER JOIN

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the results will contain ‘nulls’.


SQL SYNTAX:


AVIDO SYNTAX:


SELECT * FROM TableA A

LEFT OUTER JOIN TableB B

ON A.name = B.name




RESULTS:




ID

Name

1

Apple

2

Pie

3

Cinnamon

4

Marshmallow




ID

Name

2

Apple


Null

4

Cinnamon


Null







VENN DIAGRAM:





    • Related Articles

    • Avido BI Installation Guide

           Avido BI  Installation Guide   Version 2017.10.1            Document Information    | ​Notices    Copyright  Avido® is a brand name of the Glantus Group. Glantus is a registered trademark of Glantus Group Limited.    Other product and company ...