Back

Codeburst

Top 10 JavaScript errors from 1000+ projects (and how to avoid them)

2018-05-16 14:57:42

By Jason Skowronski @ Rollbar

To give back to our community of developers, we looked at our database of thousands of projects and found the top 10 errors in JavaScript. We’re going to show you what causes them and how to prevent them from happening. If you avoid these “gotchas,” it’ll make you a better developer.

Because data is king, we collected, analyzed, and ranked the top 10 JavaScript errors. Rollbar collects all the errors for each project and summarizes how many times each one occurred. We do this by grouping errors according to their fingerprints. Basically, we group two errors if the second one is just a repeat of the first. This gives users a nice overview instead of an overwhelming big dump like you’d see in a log file.

We focused on the errors most likely to affect you and your users. To do this, we ranked errors by the number of projects experiencing them across different companies. If we looked only at the total number of times each error occurred, then high-volume customers could overwhelm the data set with errors that are not relevant to most readers.

Here are the top 10 JavaScript errors:

Each error has been shortened for easier readability. Let’s dive deeper into each one to determine what can cause it and how you can avoid creating it.

1. Uncaught TypeError: Cannot read property

If you’re a JavaScript developer, you’ve probably seen this error more than you care to admit. This one occurs in Chrome when you read a property or call a method on an undefined object. You can test this very easily in the Chrome Developer Console.

This can occur for many reasons, but a common one is improper initialization of state while rendering the UI components. Let’s look at an example of how this can occur in a real-world app. We’ll pick React, but the same principles of improper initialization also apply to Angular, Vue or any other framework.

class Quiz extends Component {
componentWillMount() {
axios.get('/thedata').then(res => {
this.setState({items: res.data});
});
}
  render() {
return (
<ul>
{this.state.items.map(item =>
<li key={item.id}>{item.name}</li>
)}
</ul>
);
}
}

There are two important things realize here:

  1. A component’s state (e.g. this.state) begins life as undefined.
  2. When you fetch data asynchronously, the component will render at least once before the data is loaded — regardless of whether it’s fetched in the constructor, componentWillMount or componentDidMount. When Quiz first renders, this.state.items is undefined. This, in turn, means ItemList gets items as undefined, and you get an error – "Uncaught TypeError: Cannot read property ‘map’ of undefined" in the console.

This is easy to fix. The simplest way: Initialize state with reasonable default values in the constructor.

class Quiz extends Component {
// Added this:
constructor(props) {
super(props);
    // Assign state itself, and a default value for items
this.state = {
items: []
};
}
  componentWillMount() {
axios.get('/thedata').then(res => {
this.setState({items: res.data});
});
}
  render() {
return (
<ul>
{this.state.items.map(item =>
<li key={item.id}>{item.name}</li>
)}
</ul>
);
}
}

The exact code in your app might be different, but we hope we’ve given you enough of a clue to either fix or avoid this problem in your app. If not, keep reading because we’ll cover more examples for related errors below.

2. TypeError: ‘undefined’ is not an object (evaluating

This is an error that occurs in Safari when you read a property or call a method on an undefined object. You can test this very easily in the Safari Developer Console. This is essentially the same as the above error for Chrome, but Safari uses a different error message.

3. TypeError: null is not an object (evaluating

This is an error that occurs in Safari when you read a property or call a method on a null object. You can test this very easily in the Safari Developer Console.

Interestingly, in JavaScript, null and undefined are not the same, which is why we see two different error messages. Undefined is usually a variable that has not been assigned, while null means the value is blank. To verify they are not equal, try using the strict equality operator:

One way this error might occur in a real world example is if you try using a DOM element in your JavaScript before the element is loaded. That’s because the DOM API returns null for object references that are blank.

Any JS code that executes and deals with DOM elements should execute after the DOM elements have been created. JS code is interpreted from top to down as laid out in the HTML. So, if there is a tag before the DOM elements, the JS code within script tag will execute as the browser parses the HTML page. You will get this error if the DOM elements have not been created before loading the script.

In this example, we can resolve the issue by adding an event listener that will notify us when the page is ready. Once the addEventListener is fired, the init()method can make use of the DOM elements.

<script>
function init() {
var myButton = document.getElementById("myButton");
var myTextfield = document.getElementById("myTextfield");
myButton.onclick = function() {
var userName = myTextfield.value;
}
}
document.addEventListener('readystatechange', function() {
if (document.readyState === "complete") {
init();
}
});
</script>
<form>
<input type="text" id="myTextfield" placeholder="Type your name" />
<input type="button" id="myButton" value="Go" />
</form>

4. (unknown): Script error

The Script error occurs when an uncaught JavaScript error crosses domain boundaries in violation of the cross-origin policy. For example, if you host your JavaScript code on a CDN, any uncaught errors (errors that bubble up to the window.onerror handler, instead of being caught in try-catch) will get reported as simply “Script error” instead of containing useful information. This is a browser security measure intended to prevent passing data across domains that otherwise wouldn’t be allowed to communicate.

To get the real error messages, do the following:

1. Send the Access-Control-Allow-Origin header
Setting the Access-Control-Allow-Origin header to * signifies that the resource can be accessed properly from any domain. You can replace * with your domain if necessary: for example, Access-Control-Allow-Origin: www.example.com. However, handling multiple domains gets tricky, and may not be worth the effort if you’re using a CDN due to caching issues that may arise. See more here.

Here are some examples on how to set this header in various environments:

Apache
In the folders where your JavaScript files will be served from, create an .htaccessfile with the following contents:

Header add Access-Control-Allow-Origin "*"

Nginx
Add the add_header directive to the location block that serves your JavaScript files:

location ~ ^/assets/ {
add_header Access-Control-Allow-Origin *;
}

HAProxy
Add the following to your asset backend where JavaScript files are served from:

rspadd Access-Control-Allow-Origin:\ *

2. Set crossorigin=”anonymous” on the script tag
In your HTML source, for each of the scripts that you’ve set the Access-Control-Allow-Origin header for, set crossorigin="anonymous" on the SCRIPT tag. Make sure you verify that the header is being sent for the script file before adding the crossorigin property on the script tag. In Firefox, if the crossorigin attribute is present but the Access-Control-Allow-Origin header is not, the script won’t be executed.

5. TypeError: Object doesn’t support property

This is an error that occurs in IE when you call an undefined method. You can test this in the IE Developer Console.

This is equivalent to the error “TypeError: ‘undefined’ is not a function” in Chrome. Yes, different browsers can have different error messages for the same logical error.

This is a common problem for IE in web applications that employ JavaScript namespacing. When this is the case, the problem 99.9% of the time is IE’s inability to bind methods within the current namespace to the this keyword. For example, if you have the JS namespace Rollbar with the method isAwesome.Normally, if you are within the Rollbar namespace you can invoke the isAwesomemethod with the following syntax:

this.isAwesome();

Chrome, Firefox and Opera will happily accept this syntax. IE, on the other hand, will not. Thus, the safest bet when using JS namespacing is to always prefix with the actual namespace.

Rollbar.isAwesome();

6. TypeError: ‘undefined’ is not a function

This is an error that occurs in Chrome when you call an undefined function. You can test this in the Chrome Developer Console and Mozilla Firefox Developer Console.

As JavaScript coding techniques and design patterns have become increasingly sophisticated over the years, there’s been a corresponding increase in the proliferation of self-referencing scopes within callbacks and closures, which are a fairly common source of this/that confusion.

Consider this example code snippet:

function clearBoard(){
alert("Cleared");
}
document.addEventListener("click", function(){
this.clearBoard(); // what is “this” ?
});

If you execute the above code and then click on the page, it results in the following error “Uncaught TypeError: this.clearBoard is not a function”. The reason is that the anonymous function being executed is in the context of the document, whereas clearBoard is defined on the window.

A traditional, old-browser-compliant solution is to simply save your reference to this in a variable that can then be inherited by the closure. For example:

var self=this;  // save reference to 'this', while it's still this!
document.addEventListener("click", function(){
self.clearBoard();
});

Alternatively, in the newer browsers, you can use the bind() method to pass the proper reference:

document.addEventListener("click",this.clearBoard.bind(this));

7. Uncaught RangeError: Maximum call stack

This is an error that occurs in Chrome under a couple of circumstances. One is when you call a recursive function that does not terminate. You can test this in the Chrome Developer Console.

It may also happen if you pass a value to a function that is out of range. Many functions accept only a specific range of numbers for their input values. For example, Number.toExponential(digits) and Number.toFixed(digits) accept digits from 0 to 20, and Number.toPrecision(digits) accepts digits from 1 to 21.

var a = new Array(4294967295);  //OK
var b = new Array(-1); //range error
var num = 2.555555;
document.writeln(num.toExponential(4)); //OK
document.writeln(num.toExponential(-2)); //range error!
num = 2.9999;
document.writeln(num.toFixed(2)); //OK
document.writeln(num.toFixed(25)); //range error!
num = 2.3456;
document.writeln(num.toPrecision(1)); //OK
document.writeln(num.toPrecision(22)); //range error!

8. TypeError: Cannot read property ‘length’

This is an error that occurs in Chrome because of reading length property for an undefined variable. You can test this in the Chrome Developer Console.

You normally find length defined on an array, but you might run into this error if the array is not initialized or if the variable name is hidden in another context. Let’s understand this error with the following example.

var testArray= ["Test"];
function testFunction(testArray) {
for (var i = 0; i < testArray.length; i++) {
console.log(testArray[i]);
}
}
testFunction();

When you declare a function with parameters, these parameters become local ones. This means that even if you have variables with names testArray, parameters with the same names within a function will still be treated as local.

You have two ways to resolve your issue:

  1. Remove parameters in the function declaration statement (it turns out you want to access those variables that are declared outside of the function, so you don’t need parameters for your function):
var testArray = ["Test"];

/* Precondition: defined testArray outside of a function */
function testFunction(/* No params */) {
for (var i = 0; i < testArray.length; i++) {
console.log(testArray[i]);
}
}

testFunction();

2. Invoke the function passing it the array that we declared:

var testArray = ["Test"];

function testFunction(testArray) {
for (var i = 0; i < testArray.length; i++) {
console.log(testArray[i]);
}
}

testFunction(testArray);

9. Uncaught TypeError: Cannot set property

When we try to access an undefined variable it always returns undefined and we cannot get or set any property of undefined. In that case, an application will throw “Uncaught TypeError cannot set property of undefined.”

For example, in the Chrome browser:

If the test object does not exist, error will throw “Uncaught TypeError cannot set property of undefined.”

10. ReferenceError: event is not defined

This error is thrown when you try to access a variable that is undefined or is outside the current scope. You can test it very easily in Chrome browser.

If you’re getting this error when using the event handling system, make sure you use the event object passed in as a parameter. Older browsers like IE offer a global variable event, and Chrome automatically attaches the event variable to the handler. Firefox will not automatically add it. Libraries like jQuery attempt to normalize this behavior. Nevertheless, it’s best practice to use the one passed into your event handler function.

document.addEventListener("mousemove", function (event) {
console.log(event);
})

Conclusion

It turns out a lot of these are null or undefined errors. A good static type checking system like Typescript could help you avoid them if you use the strict compiler option. It can warn you if a type is expected but has not been defined. Even without Typescript, it helps to use guard clauses to check whether objects are undefined before using them.

We hope you learned something new and can avoid errors in the future, or that this guide helped you solve a head scratcher. Nevertheless, even with the best practices, unexpected errors do pop up in production. It’s important to have visibility into errors that affect your users, and to have good tools to solve them quickly.

Rollbar gives you visibility to production JavaScript errors and gives you more context to solve them quickly. For example, it offers additional debugging features like telemetry which tells you what happened on the user’s browser leading up to the error. That’s insight you don’t have outside of your local developer console. Learn more in Rollbar’s full list of features for JavaScript applications.

If you haven’t already, signup for a 14-day free trial of Rollbar and let us help you take control of impactful JavaScript errors. :-)

This article was originally published on Rollbar.com and has been reposted with permission.

If this post was helpful, please click the clap 👏 button below a few times to show your support! ⬇⬇


Top 10 JavaScript errors from 1000+ projects (and how to avoid them) was originally published in codeburst on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read more

100 Days Full-Stack Challenge

2018-05-16 14:56:26

An honest attempt to teach myself programming by publicly sharing my progress, every day for the next 100 days.

Hello Internet,

You inspire me! Just like so many of us, I am an avid consumer of the web. Almost everything that I really know, I owe it to the people who contributed to the vast ocean of content and those who made all the content accessible through products they help build.

Someday, I’d like to play my part by building products that hopefully solve meaningful problems, but here is my limitation: I don’t know how to program!

So, this is an honest attempt to teach myself programming (one full-stack), by publicly sharing my progress, every day for the next 100 days!

Why learn programming when there are other ways of realizing a product?

I know! I know! Some might say that I probably don’t need to learn programming to be able to build consumer internet products, especially when I am not inventing a new technology. I could partner with a developer, build a team or even hire freelancers to get the job done. I have and I am doing all that — more about it some other time — however, I still want to learn how to program, and here’s why:

  1. There is something magical about being able to create stuff yourself that cannot be replaced by outsourcing the task. I know this because I have experienced it in other fields. For example, years ago I taught myself how to play the guitar. It was a painfully slow and uninteresting process at first. But somehow, I kept at it and eventually started playing decently well, formed a 5-piece metal band and even wrote original music.
    Today, the band is non-existent and I have moved far away from rock music but the point is, it was an extremely rewarding experience to be able to share your ideas and creativity with others. Back then, my creative outlet was the guitar and music, but I don’t see why programming would be any different, in that regard. Arguably, it is all that but on a potentially global stage.
  2. Involving others, somehow, makes things too formal too soon. As a non-technical person, to convince technical folks to come work on a problem that you are interested in, takes a lot! You really have to show the passion, the mission, the vision, yadi yada to convince someone to leave what they are doing and join you. And of course, you only want to partner with people who you really like. So, from all sides, the bar is very high. I have found this to be a deterrent, something that slows me down, considerably.
    I’m looking for a process that is super lightweight and low commitment, through which I can test my assumptions and solutions for problems that I find interesting before I say “I’m all in!”. I might spend a couple of weeks on one, if it doesn’t work, I learn, salvage what I can and move on to the next. Something as quick and straightforward as picking up a guitar and writing music, and I’m confident that if I’m able to go over this initial hurdle of getting started, programming would be exactly that and more.
  3. Lastly, why not?

The Challenge

Rules:

  1. Every day, from May 7th through August 15th, I’ll spend a couple of hours learning from a pre-defined syllabus (below).
  2. Every day, I’ll share a quick status update on the progress I made that day here on Medium. Including, but not limited to, how I felt about that day, any roadblocks and lessons learned.

Goal:

After completing this challenge, I should be able to build a full-stack functional application from start to finish without the need to search for any basic syntax or ask for help. If successful, all I’d need to worry about is execution and not ‘how to execute’.

Syllabus:

Stack: MERN (MongoDB, Express, React, Node)

  1. Day 1–10 | Codecademy Immersive Program — Learn React.js
  2. Day 11–15 | Codecademy Immersive Program — Brush up Javascript
  3. Day 16–50 | Udemy — This full stack development course
  4. Day 51–55 | Evaluate progress
  5. Day 56–100 | Still open

The syllabus is, but of course, customized to my exact needs, gaps and goals.

This is not my first attempt at learning how to code. I have taken online tutorials in the past and I am somewhat familiar with HTML, CSS and vanilla JS.

My problem always has been:

  1. Internalizing the syntax. In the past, I never made an effort to truly internalize the syntax. I was just curious to learn the logic and once I got that, I’d move on. So now, even though I have some idea on how things work and can probably talk intelligently about it, I really cannot put together something more than a basic static HTML / CSS web page.
  2. Making one full circle. My knowledge is patchy at best. I know bits and pieces, here and there. I have a lot of gaps in my understanding, and I cannot make one full circle and build something functional.
  3. Lastly, keeping at it. I have tried learning programming many times in the past, but have never been able to keep at it and see it through. Started multiple online tutorials, left most of them in the middle only to come back and start another tutorial a few months later.

By publicly stating my intentions and committing to sharing my progress every day, I am hoping that this time I will be able to go over the hump, make one full circle and be able to internalize the process of building a full-stack application from start to finish.

Excited to get this started. If you are interested in coming along for the journey, follow me here on medium for updates.

✉️ Subscribe to CodeBurst’s once-weekly Email Blast, 🐦 Follow CodeBurst on Twitter, view 🗺️ The 2018 Web Developer Roadmap, and 🕸️ Learn Full Stack Web Development.

100 Days Full-Stack Challenge was originally published in codeburst on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read more

Javascript with map, reduce, filter and ES6

2018-05-16 14:54:56

If you are a fan of javascript and use it daily then you will love this 😃

Javascript is a language that give freedom of writing code in any style, from imperative to declarative styles. Most programmer use imperative because either they are coming from OOPs background, may be they love it or they are not familiar with other style. Before we dive into the declarative style which is FP, let’s understand the differences in two by looking at an example(If you already know the difference then you may skip few paragraphs)

Imperative

Imperative style is cool but imagine what if there is a complex mathematics logic here then size of code and the readability will suck. It increases the cognitive load when reading, and over time makes it easier to faulter in reasoning and logic.

Also, the main complexity of this code snippet derives from the fact that instead of telling the computer what we want it to do, we are instructing it on how to do it.

Declarative

Now, this looks pretty clean, shorter, expressive, concise code, less error prone, easier to maintain and easier to debug. We are telling computer what we want it to do rather how to do it.

Declarative approach are easily optimisable at complier end and also have less side effects.

Note: if you are concerned about the performance of above two and other javascript function (map, reduce, filter, find) then you should read here for small data set and can view here for large data set(100–1000000)

Without more delay, let’s start the real action with most used Javascript function for FP.

Map

// definition 
collection.map((currentValue, index) => {
// Return element for newArray
});
// example
const arr = [1,2,3,4,5];
const newArray = arr.map(i => i*10);
// return a new array with all value as multiple of 10;

Map works on an array and return an array that’s it. Above code snippet works on an collection i.e an array and takes a callback with current iteration value, index as arguments and return a new array.

Note: Maps are well suited for change/transforming whole array rather than breaking the flow for some conditions, Map suck’s performance wise, check out here but are easy to be used for small data sets.

Reduce

// definition 
collection.reduce((accumulator, item, index) => {
// logic to perform to get accumulator as a return value
}, initialValue for accumulator);
// example
const arr = [1,2,3,4,5];
const total = arr.reduce((acc, item) => acc+= item, 0);
// return a total as 15

Reduce works on an array but can return anything you want it to return. As the name speaks for itself it can be reduce to anything and can behave like map, find, filter or any other javascript function. The above code snippet works on an array and reduce to compute the total value of item of array.

Explanation of example above : On reduce first run, acc is assigned a 0 value and then acc+= item i.e acc = acc+item which will compute to0+1 i.e 1. This 1 will be acc value for next iteration and this continues until we are done with all array items.

Find

// definition 
collection.find((item) => {
// return first element that satisfy the condition
});
// example
const arr = [1,2,8,4,5];
const value = arr.find(i => i%4 == 0);
// return the first value i.e 8

Find works on an array and return the first element that satisfy the condition in function.

Note: Easy, simple but not efficient on large data set, why ? look here

Filter

// definition 
collection.filter((currentValue, index) => {
// logic to filter array on
});
// example
const arr = [1,2,3,4,5];
const newArray = arr.filter(i => i%2 == 0);
// return a new array with value [2, 4]

Filter works on array return an array for filtered items.

Lets use them for some real world scenarios + some ES6. (lets try some ARMD on below object keys)

Wondering what is ARMD its Add, Read, Modify, Delete, its cool to coin your own jargon 😄

We will use users as array for further examples

1. ARMD — Adding a new element to users

const newUser = {
id: 4,
name: "Denomer Crazy",
username: "crazy.1",
email: "deno@crazy.com",
phone: "",
website: "crazy.app",
password: "crazed_checker"
};
const newData = [...users, newUser]; // add element at last
or
const newData = [newUser, ...users]; // add element at first
or
const newData = users.concat(newUser) // the old way

The use of es6 spread operator make super easy to add elements to array. We can use spread operator to concat two different array, modify shape of objects or add dynamic key value pairs etc.

const hobbies = ['chess', 'pool'];
const newUsers = users.map(u => ({...u, hobbies}))
// this will add hobbies to users array and return newUsers array

2. ARMD — Get email address, phone number and website of users into new array

const contactInfo = users.map(({email, website, phone}) => ({email, website, phone}));

The use es6 of destructuring of object keys and map to get the contact info array for user.

3. ARMD — Find and replace value for key of objects

const newUsers = users.map(u => u.id == 2? ({...u, name: 'te'}): u);
// this will return newUsers with all user having name 'te'

4. ARMD —Delete some key’s from object

Note: We will actually not delete the key but return a new object, if you want to delete the key use delete operator, here we are considering object immutability.

To delete keys there are lot of ways but we will look at the most easy, single lined. Lets try to delete website from users.

const newUsers = users.map({id, email, name, username, phone, password} => ({id, email, username, email, phone, password}));
// will return an array with all keys other than website

Above code seems to be practically difficult to code for big objects.

const newUsers = users.map(u => Object.keys(u).reduce((newObj, key) => key != 'website' ? { ...newObj, [key]: u[key]} : newObj, {}));

We map through the users and then on each user we do a reduce and form a new object (newObj) and then check for website key, if its a website we return the previously formed newObj, if not then we do a spread operator and add require key to obj and finally return newObj.

If this post was helpful, please click the clap 👏 button below to show your support! Also, recommend and share to help others find it!

Next article will be on optimising the javascript code and what all key point to remember for speed up 🚀 with current v8. If you want to read it or see my previous articles and code on javascript then please follow me on Medium and Github.

THANK YOU!

✉️ Subscribe to CodeBurst’s once-weekly Email Blast, 🐦 Follow CodeBurst on Twitter, view 🗺️ The 2018 Web Developer Roadmap, and 🕸️ Learn Full Stack Web Development.

Javascript with map, reduce, filter and ES6 was originally published in codeburst on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read more

INNER JOIN in MySQL with examples.

2018-05-16 14:54:46

To normalize data, we often store it in several tables within a given database. While querying those tables individually can provide answers to numerous questions around that data, more times than not, it leaves us ‘coming up short’ for the complete answer we need.
That poses the question of, how do you extract all this data from several tables that depend on one another to provide data-driven answers?
One tried and true method is to use a JOIN operation.

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and Database:

  • Xubuntu Linux 16.04.3 LTS (Xenial Xerus)
  • MySQL 5.7.22
Photo by pepe nero on Unsplash

There are multiple types of JOIN‘s in MySQL (and SQL in general), however the focus for this blog post will be the INNER JOIN.

This type of JOIN enables you to link columns from multiple tables on matching values.

I will target these three tables from the mock DVD rental Sakila database.

The city, country, and address tables.

I need to narrow down a result set to something more manageable in regards to output, brevity, and on-screen display for this blog post. So to start things off, I’ll jump right into joining on two of those tables.

I’ll center the query around a country with a city count of 6.

The below INNER JOIN query, along with handy use of COUNT(), GROUP BY, and HAVING will give me something to work with.

mysql> SELECT COUNT(city), country
-> FROM city
-> INNER JOIN country
-> ON city.country_id = country.country_id
-> GROUP BY country
-> HAVING COUNT(city) = 6;
+ — — — — — — -+ — — — — — +
| COUNT(city) | country |
+ — — — — — — -+ — — — — — +
| 6 | Colombia |
| 6 | Egypt |
| 6 | Ukraine |
| 6 | Vietnam |
+ — — — — — — -+ — — — — — +
4 rows in set (0.00 sec)

Let’s look at the INNER JOIN and what it does.
You will notice that both tables share a sort of ‘common’ column named country_id.
In table country, this is the PRIMARY KEY, and in table city, it’s a FOREIGN KEY.
We can use these columns to ‘link’ or JOIN these tables where their values match.
To do that, we use the ON clause as part of the JOIN (See more below).

To note:

  • In MySQL the INNER keyword is optional for this type of JOIN. According to the documentation in section 13.2.9.2 Join Syntax, CROSS JOIN is interchangeable syntax as well. I have tested (not shown) all three variations: JOIN, INNER JOIN, and CROSS JOIN; All are compatible with the query above. Important: This behavior is applicable to MySQL only and not compliant with standard SQL.
  • The ON clause names a JOIN condition in how the tables are linked on a matching row.
  • Without the HAVING and GROUP BY clauses, this query will return all matching rows. To reiterate, I mainly use them here in the query to demonstrate with better screen output for blog posts. Those mentioned clauses are not mandatory or even JOIN syntax.
    However, it is perfectly fine and often useful to filter with any one or a combination of the available clauses when used in context appropriately.

I’ll also directly provide important information from the official documentation (link in closing section) located in that same 13.2.9.2 JOIN Syntax section as mentioned above.

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.

In other words, we use ON in this query to specify a match condition for rows where the country_id column value from table city, is exactly the same as the country_id column in table country.

This is a boolean condition that executes in identical fashion as that of a WHERE clause.
Basically, a test for truth.
If a row meets the specified condition, it is deemed to be true.

I have an idea.
How about we include the country_id column in the SELECT list for even more clarity:

mysql> SELECT COUNT(city), country, country_id
-> FROM city
-> INNER JOIN country
-> ON city.country_id = country.country_id
-> GROUP BY country
-> HAVING COUNT(city) = 6;
ERROR 1052 (23000): Column ‘country_id’ in field list is ambiguous

Well, which country_id did we mean? That’s interesting. MySQL would also like to know.

Keep this in mind when joining and querying from multiple tables that may have columns with identical names.

You have to be specific and provide clarity to the database (MySQL) of which column you are requesting.
You may be thinking column country didn’t throw this ambiguous error? That’s correct.

Because that column only exists in the country table and MySQL knows without a doubt which one we are referencing.

So how do you avoid these ambiguous errors?
You ‘qualify’ the identically named columns.
By prefixing the column names with their respective parent table name, this avoids any confusion and MySQL will then know which column(s) from which table(s) you requested.

I’ll include the country_id column from the city table in this query:

mysql> SELECT COUNT(city), country, city.country_id
-> FROM city
-> INNER JOIN country
-> ON city.country_id = country.country_id
-> GROUP BY country, city.country_id
-> HAVING COUNT(city) = 6;
+ — — — — — — -+ — — — — — + — — — — — — +
| COUNT(city) | country | country_id |
+ — — — — — — -+ — — — — — + — — — — — — +
| 6 | Colombia | 24 |
| 6 | Egypt | 29 |
| 6 | Ukraine | 100 |
| 6 | Vietnam | 105 |
+ — — — — — — -+ — — — — — + — — — — — — +
4 rows in set (0.01 sec)

By specifying the country_id from table city, MySQL knows exactly which column from which table we want.

A touch on aliasing… Aliasing is an alternate syntax for referencing table names.

Instead of using the full table name for the qualifying prefix, you can alias a table name in the FROM clause:

mysql> SELECT COUNT(ci.city), co.country, ci.country_id
-> FROM city AS ci
-> INNER JOIN country AS co
-> ON ci.country_id = co.country_id
-> GROUP BY co.country, ci.country_id
-> HAVING COUNT(ci.city) = 6;
+ — — — — — — — — + — — — — — + — — — — — — +
| COUNT(ci.city) | country | country_id |
+ — — — — — — — — + — — — — — + — — — — — — +
| 6 | Colombia | 24 |
| 6 | Egypt | 29 |
| 6 | Ukraine | 100 |
| 6 | Vietnam | 105 |
+ — — — — — — — — + — — — — — + — — — — — — +
4 rows in set (0.00 sec)

We can use table referencing, with one of two syntax structures:

table_name AS desired_reference_name
table_name desired_reference_name

So in this query,

FROM city AS ci

and

FROM city ci

are equivalent and perfectly acceptable in this context.

* Note: As mentioned, the AS keyword is optional in this context. However, including it could enhance readability and clarity. Therefore, I strive to consistently include it in these particular use cases.

Now, anywhere you reference a column from either table city or country, use ci or co respectively in lieu of the full table name.

There is an alternate clause available for specifying the JOIN condition.

The USING clause.
Column(s) named in the USING clause, must be present in both tables.
Here is an example:

mysql> SELECT COUNT(city), country
-> FROM city AS ci
-> INNER JOIN country AS co
-> USING(country_id)
-> GROUP BY co.country
-> HAVING COUNT(ci.city) = 6;
+ — — — — — — -+ — — — — — +
| COUNT(city) | country |
+ — — — — — — -+ — — — — — +
| 6 | Colombia |
| 6 | Egypt |
| 6 | Ukraine |
| 6 | Vietnam |
+ — — — — — — -+ — — — — — +
4 rows in set (0.00 sec)

* Note: The parenthesis surrounding country_id are required in the USING clause.
While not shown here, USING can accept multiple comma-separated column names for the JOIN condition.

What if your results set has to come from more than 2 tables?
Can JOIN‘s accomplish that?
Sure can.

In the picture provided at the beginning of the post, you see a third address table. Let’s add that table into the mix here.

Suppose we need to determine all the city names and addresses available within the database for the country 'Egypt'.

This can be solved with a multi-table JOIN.

The below query includes the necessary JOIN‘s to return that particular result set:

So just what is going on with all this joining?
Let’s look at our example query line by line and better understand the operation.

mysql> SELECT ci.city, a.address, a.postal_code, co.country
-> FROM city AS ci
-> INNER JOIN address AS a
-> ON ci.city_id = a.city_id
-> INNER JOIN country AS co
-> ON ci.country_id = co.country_id
-> WHERE co.country = ‘Egypt’;
  • Line 1 provides the columns we are requesting via the SELECT clause.
  • Lines 2 and 3: MySQL is combining every row present in the city table (named in the FROM clause) with every row in the address table (named as the table to JOIN).
  • Line 4: At this point, the combined results set may not be the desired results you are after. Here is where the ON or USING clause comes into the picture. By employing ON, this sets up a ‘match’ condition that must be met (true) for each ‘matching/linked’ row for both tables involved in the JOIN condition. Two mini-pointers are worth a mention here:
  • This condition should be on ‘like’ data types (e.g., 2 = 2, ‘Programmer’ = ‘Programmer’, ‘2012–10–08’ = ‘2012–10–08’).
  • However, they need to also make sense. In other words, just because the strings ‘Egypt’ and ‘Columbia’ are both in fact, character data types; in this context, a match on them does not make sense and likely will not yield correct results. Remember, this is a test for truth. ‘Egypt’ = ‘Egypt’ will return true, while ‘Egypt’ = ‘Columbia’ will not.
  • Rinse and repeat as lines 5 and 6 are identical to the operations from lines 2 and 3. All rows from table city are joined to all rows in table country. Then the ON clause specifies that the country_id column value from both tables, must match or be equal.
  • Line 7: Here, the WHERE clause filters rows that must meet specific criteria to make the final result set returned from the database. For this query, the country column from table country must have the value 'Egypt' on a ‘matched’ row.

Leveraging JOIN‘s, we can combine linked or related data from multiple tables, returning meaningful metrics and insights.
JOIN‘s can assist in answering questions like i.e., authors and books they wrote, manufactures and the cars they make, musicians and albums they’ve released, etc…
It should be noted although not covered in this blog post, JOIN‘s are also applicable to multi-table UPDATE and DELETE operations.
Explore the official MySQL 5.7 Online Manual for more information.

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive notifications for the latest post from “Digital Owl’s Prose” via email, please subscribe by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage!
Be sure and visit the “Best Of” page for a collection of my best blog posts while you are there!

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

Originally published at joshuaotwell.com on May 9, 2018.

✉️ Subscribe to CodeBurst’s once-weekly Email Blast, 🐦 Follow CodeBurst on Twitter, view 🗺️ The 2018 Web Developer Roadmap, and 🕸️ Learn Full Stack Web Development.

INNER JOIN in MySQL with examples. was originally published in codeburst on Medium, where people are continuing the conversation by highlighting and responding to this story.

Read more