{"id":3571,"date":"2018-06-18T07:20:54","date_gmt":"2018-06-18T14:20:54","guid":{"rendered":"https:\/\/www.springboard.com\/?p=3571"},"modified":"2024-01-10T21:55:34","modified_gmt":"2024-01-11T05:55:34","slug":"joining-data-tables","status":"publish","type":"post","link":"https:\/\/www.springboard.com\/blog\/data-science\/joining-data-tables\/","title":{"rendered":"4 Types of SQL Joins for Database Tables"},"content":{"rendered":"\n

This post was written by Nicole Hitner, a content strategist at Exago, Inc., producers of embedded business intelligence for software companies<\/a>. She manages the company\u2019s content marketing, writes for their blog<\/a>, and assists the product design team in continuing to enhance Exago BI.<\/em><\/p>\n\n\n\n

SQL Joins Explained<\/h2>\n\n\n\n

A relational database is ultimately just a collection of data tables, each of which is useful on its own, but exponentially more useful when joined to its neighbors. Data scientists-to-be will have a leg up on the competition if they go into their first SQL course<\/a> with a basic understanding of the four essential join types, which not only <\/span>merge <\/span><\/i>data on output but also <\/span>filter <\/span><\/i>it to display a specific subset of records. Before we explore how joins define the relationships between tables, however, we first need to understand what a <\/span>key field<\/b> is.<\/span><\/p>\n\n\n\n

Key Fields<\/span><\/h3>\n\n\n\n

We will discuss two types of key fields: primary keys<\/strong> and foreign keys<\/strong>. A primary key is a field or column that uniquely identifies each row or record in the table. A table must have exactly one primary key to qualify as relational, but that key can be composed of multiple columns.<\/p>\n\n\n\n

A foreign key, by contrast, is one or more fields or columns that corresponds to the primary key of another table. Foreign keys are what make it possible to join tables to each other. <\/span><\/p>\n\n\n\n

In the set of tables below, for example, kid_id is the primary key for KIDS and chore_id is a foreign key corresponding to a field of the same name in CHORES. Note that CHORES does not have a foreign key for KIDS.<\/span><\/p>\n\n\n\n

\"data<\/figure>\n\n\n\n

You might wonder why kid_name isn\u2019t the primary key for the KIDS table. Its values are indeed unique per record, but what happens if we begin adding records to the table? As soon as a new, different Steve is added to the KIDS table, kid_name stops being unique per record. This is why it\u2019s a good idea for the primary key to have only one job: locating records.<\/span><\/p>\n\n\n\n

\"data<\/figure>\n\n\n\n

Because KIDS has a foreign key corresponding to a primary key in CHORES (chore_id), we know that we can join these two tables together to make one. <\/span>How<\/span><\/i> that happens, however, will depend on the join type.<\/span><\/p>\n\n\n\n

Full Outer Joins<\/span><\/h3>\n\n\n\n

The most basic database join is what\u2019s known as a full outer join. The output contains all the primary ids in both tables and all fields in both tables, regardless of whether the records have matches in the opposite table. If, in the diagram below, we represent data that will appear in the output using the color blue, we can see that all records from both tables will be returned.<\/span><\/p>\n\n\n\n

\"full<\/figure>\n\n\n\n

Let\u2019s look at a new example. Consider the following tables found in a university database.<\/span><\/p>\n\n\n\n

\"data<\/figure>\n\n\n\n

If we do a full outer SQL join on rm_id, keeping all primary ids in both tables as well as all columns, ordering by course_id ascending, we end up with the following output:<\/span><\/p>\n\n\n\n

\"outer<\/figure>\n\n\n\n

Because Developmental Psychology 2 has not yet been assigned a room, it does not have a match in the ROOM table. HUM311 has likewise not been assigned a course, so it has no match in the COURSE table. As a result, there are lots of null or empty values in the output, and this is fairly typical of full outer joins. The goal is to be all-inclusive, which isn\u2019t the case for other join types. If, for example, we were only interested in courses with rooms, we might use a left outer join instead.<\/span><\/p>\n\n\n

Get To Know Other\tData Science Students<\/h4>
\"Esme<\/a>

Esme Gaisford<\/p>

Senior Quantitative Data Analyst at Pandora<\/p><\/div>

<\/div>

Read Story<\/a><\/p><\/div><\/div>

\"Aaron<\/a>

Aaron Pujanandez<\/p>

Dir. Of Data Science And Analytics at Deep Labs<\/p><\/div>

Read Story<\/a><\/p><\/div><\/div>

\"Corey<\/a>

Corey Wade<\/p>

Founder And Director at Berkeley Coding Academy<\/p><\/div>

Read Story<\/a><\/p><\/div><\/div><\/div><\/div>\n\n\n\n

Left and Right Outer Joins<\/span><\/h3>\n\n\n\n

Left outer joins use all the primary ids of the left table and all fields of both tables. In the diagram below, we see that all records of the left table appear in the output, along with those in the right table, as long as they have matches in the left.<\/span><\/p>\n\n\n\n

\"left<\/figure>\n\n\n\n

Referring back to our collegiate example, a left outer join of the two original tables would yield the following:<\/span><\/p>\n\n\n\n

\"left<\/figure>\n\n\n\n

Note that HUM311 is nowhere to be found on this table. That is because it neither has a primary key in the left table (because it\u2019s a room, not a course) nor is it associated with a course. We\u2019re beginning to see how joins can act as filters by restricting which records return from the query.<\/span><\/p>\n\n\n\n

The mirror opposite of a left outer join is, as you might have guessed, a right outer join. In a right outer join, all the primary ids of the right table appear in the output along with all columns from both tables. <\/span><\/p>\n\n\n\n

\"right<\/figure>\n\n\n\n

This time, it\u2019s the PSY302 class that doesn\u2019t make the cut.<\/span><\/p>\n\n\n\n

\"right<\/figure>\n\n\n\n

All the rooms automatically make it into the output, but the only courses that will be returned, in this case, are those with rooms assigned. PSY302 has not yet been assigned a room and is therefore excluded from the resulting table.<\/span><\/p>\n\n\n\n

Inner SQL Joins<\/span><\/h3>\n\n\n\n

The fourth and final basic join type is an inner join. Here, the only records we want to be returned on output are those referenced in <\/span>both<\/span><\/i> tables.<\/span><\/p>\n\n\n\n

\"inner<\/figure>\n\n\n\n

So, in our courses and rooms example, courses without rooms are out, as are rooms without courses. This leaves us with just the science courses and their respective rooms:<\/span><\/p>\n\n\n\n

\"inner<\/figure>\n\n\n\n

Semi Joins and Anti Joins<\/span><\/h3>\n\n\n\n

Inner, left outer, right outer, and full outer are the four basic join types you should know when you\u2019re just getting into SQL, but there are other, less common joins to explore as well. Semi joins, unlike the joins we\u2019ve looked at so far, <\/span>don\u2019t<\/span><\/i> return all columns of both tables. Instead, they just return the columns of the left table and records with matches in the right table. If we were to diagram this concept, it might look something like this:<\/span><\/p>\n\n\n\n

\"semi<\/figure>\n\n\n\n

The output would look similar to the output resulting from an inner join, except columns specific to Table B (ROOM) would be missing:<\/span><\/p>\n\n\n\n

\"semi<\/figure>\n\n\n\n

An anti join is the opposite of a semi join; we only return records from Table A that <\/span>do not <\/span><\/i>have matches in Table B. <\/span><\/p>\n\n\n\n

\"anti<\/figure>\n\n\n\n

Since only the Psychology course has no assigned room, it would be the only record to appear on output:<\/span><\/p>\n\n\n\n

\"anti<\/figure>\n\n\n\n

Once you\u2019ve got these basic join types down pat, you can explore formulaic joins, such as those containing conditional statements, ranges, and calculations! SQL is such a versatile language<\/a> that joining options are virtually endless. Understanding these four primary concepts will help prepare you for other lessons in databases<\/a>, data science<\/a>, and analytics.<\/span> Moreover, SQL is extremely important in the field of data science<\/a>. As a result, data scientists<\/a> should have a solid foundation in SQL to handle structured data.<\/p>\n\n\n\n

Companies are no longer just collecting data. They\u2019re seeking to use it to outpace competitors, especially with the rise of AI and advanced analytics techniques. Between organizations and these techniques are the data scientists \u2013 the experts who crunch numbers and translate them into actionable strategies. The future, it seems, belongs to those who can decipher the story hidden within the data, making the role of data scientists more important than ever.<\/p>\n\n\n\n

In this article, we\u2019ll look at 13 careers in data science, analyzing the roles and responsibilities and how to land that specific job in the best way. Whether you\u2019re more drawn out to the creative side or interested in the strategy planning part of data architecture, there\u2019s a niche for you. <\/p>\n\n\n\n

Is Data Science A Good Career?<\/h2>\n\n\n\n

Yes. Besides being a field that comes with competitive salaries, the demand for data scientists continues to increase as they have an enormous impact on their organizations. It\u2019s an interdisciplinary field that keeps the work varied and interesting.<\/p>\n\n\n\n

10 Data Science Careers To Consider<\/h2>\n\n\n\n

Whether you want to change careers or land your first job in the field, here are 13 of the most lucrative data science careers to consider.<\/p>\n\n\n\n

<\/div><\/div><\/div><\/div>\n\n\n\n

<\/p>\n\n\n\n

Data Scientist<\/h3>\n\n\n\n

Data scientists represent the foundation of the data science department. At the core of their role is the ability to analyze and interpret complex digital data, such as usage statistics, sales figures, logistics, or market research \u2013 all depending on the field they operate in.<\/p>\n\n\n\n

They combine their computer science, statistics, and mathematics expertise to process and model data, then interpret the outcomes to create actionable plans for companies. <\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

A data scientist\u2019s career starts with a solid mathematical foundation, whether it\u2019s interpreting the results of an A\/B test or optimizing a marketing campaign. Data scientists should have programming expertise (primarily in Python and R) and strong data manipulation skills. <\/p>\n\n\n\n

Although a university degree is not always required beyond their on-the-job experience, data scientists need a bunch of data science courses<\/a> and certifications that demonstrate their expertise and willingness to learn.<\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

The average salary of a data scientist in the US is $156,363<\/a> per year.<\/p>\n\n\n\n

Data Analyst<\/h3>\n\n\n\n

A data analyst explores the nitty-gritty of data to uncover patterns, trends, and insights that are not always immediately apparent. They collect, process, and perform statistical analysis on large datasets and translate numbers and data to inform business decisions.<\/p>\n\n\n\n

A typical day in their life can involve using tools like Excel or SQL and more advanced reporting tools like Power BI or Tableau to create dashboards and reports or visualize data for stakeholders. With that in mind, they have a unique skill set that allows them to act as a bridge between an organization’s technical and business sides.<\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

To become a data analyst, you should have basic programming skills and proficiency in several data analysis tools. A lot of data analysts turn to specialized courses or data science bootcamps<\/a> to acquire these skills. <\/p>\n\n\n\n

For example, Coursera offers courses like Google’s Data Analytics Professional Certificate or IBM’s Data Analyst Professional Certificate, which are well-regarded in the industry. A bachelor’s degree in fields like computer science, statistics, or economics is standard, but many data analysts also come from diverse backgrounds like business, finance, or even social sciences.<\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

The average base salary of a data analyst is $76,892<\/a> per year.<\/p>\n\n\n\n

Business Analyst<\/h3>\n\n\n\n

Business analysts often have an essential role in an organization, driving change and improvement. That\u2019s because their main role is to understand business challenges and needs and translate them into solutions through data analysis, process improvement, or resource allocation. <\/p>\n\n\n\n

A typical day as a business analyst involves conducting market analysis, assessing business processes, or developing strategies to address areas of improvement. They use a variety of tools and methodologies, like SWOT analysis, to evaluate business models and their integration with technology.<\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

Business analysts often have related degrees, such as BAs in Business Administration, Computer Science, or IT. Some roles might require or favor a master\u2019s degree, especially in more complex industries or corporate environments.<\/p>\n\n\n\n

Employers also value a business analyst\u2019s knowledge of project management principles like Agile or Scrum and the ability to think critically and make well-informed decisions.<\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

A business analyst can earn an average of $84,435<\/a> per year.<\/p>\n\n\n\n

Database Administrator<\/h3>\n\n\n\n

The role of a database administrator is multifaceted. Their responsibilities include managing an organization’s database servers and application tools. <\/p>\n\n\n\n

A DBA manages, backs up, and secures the data, making sure the database is available to all the necessary users and is performing correctly. They are also responsible for setting up user accounts and regulating access to the database. DBAs need to stay updated with the latest trends in database management and seek ways to improve database performance and capacity. As such, they collaborate closely with IT and database programmers.<\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

Becoming a database administrator typically requires a solid educational foundation, such as a BA degree in data science-related fields. Nonetheless, it\u2019s not all about the degree because real-world skills matter a lot. Aspiring database administrators should learn database languages, with SQL being the key player. They should also get their hands dirty with popular database systems like Oracle and Microsoft SQL Server. <\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

Database administrators earn an average salary of $77,391<\/a> annually.<\/p>\n\n\n\n

Data Engineer<\/h3>\n\n\n\n

Successful data engineers construct and maintain the infrastructure that allows the data to flow seamlessly. Besides understanding data ecosystems on the day-to-day, they build and oversee the pipelines that gather data from various sources so as to make data more accessible for those who need to analyze it (e.g., data analysts).<\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

Data engineering is a role that demands not just technical expertise in tools like SQL, Python, and Hadoop but also a creative problem-solving approach to tackle the complex challenges of managing massive amounts of data efficiently. <\/p>\n\n\n\n

Usually, employers look for credentials like university degrees or advanced data science courses and bootcamps.<\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

Data engineers earn a whooping average salary of $125,180<\/a> per year.<\/p>\n\n\n\n

Database Architect<\/h3>\n\n\n\n

A database architect\u2019s main responsibility involves designing the entire blueprint of a data management system, much like an architect who sketches the plan for a building. They lay down the groundwork for an efficient and scalable data infrastructure. <\/p>\n\n\n\n

Their day-to-day work is a fascinating mix of big-picture thinking and intricate detail management. They decide how to store, consume, integrate, and manage data by different business systems.<\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

If you\u2019re aiming to excel as a database architect but don\u2019t necessarily want to pursue a degree, you could start honing your technical skills. Become proficient in database systems like MySQL or Oracle, and learn data modeling tools like ERwin. Don\u2019t forget programming languages – SQL, Python, or Java. <\/p>\n\n\n\n

If you want to take it one step further, pursue a credential like the Certified Data Management Professional (CDMP) or the Data Science Bootcamp by Springboard<\/a>.<\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

Data architecture is a very lucrative career. A database architect can earn an average of $165,383<\/a> per year.<\/p>\n\n\n\n

Machine Learning Engineer<\/h3>\n\n\n\n

A machine learning engineer experiments with various machine learning models and algorithms, fine-tuning them for specific tasks like image recognition, natural language processing, or predictive analytics. Machine learning engineers also collaborate closely with data scientists and analysts to understand the requirements and limitations of data and translate these insights into solutions. <\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

As a rule of thumb, machine learning engineers must be proficient in programming languages like Python or Java, and be familiar with machine learning frameworks like TensorFlow or PyTorch. To successfully pursue this career, you can either choose to undergo a degree or enroll in courses and follow a self-study approach.<\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

Depending heavily on the company’s size, machine learning engineers can earn between $125K and $187K<\/a> per year, one of the highest-paying AI careers<\/a>.<\/p>\n\n\n\n

Quantitative Analyst<\/h3>\n\n\n\n

Qualitative analysts are essential for financial institutions, where they apply mathematical and statistical methods to analyze financial markets and assess risks. They are the brains behind complex models that predict market trends, evaluate investment strategies, and assist in making informed financial decisions. <\/p>\n\n\n\n

They often deal with derivatives pricing, algorithmic trading, and risk management strategies, requiring a deep understanding of both finance and mathematics.<\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

This data science role demands strong analytical skills, proficiency in mathematics and statistics, and a good grasp of financial theory. It always helps if you come from a finance-related background. <\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

A quantitative analyst earns an average of $173,307<\/a> per year.<\/p>\n\n\n\n

Data Mining Specialist<\/h3>\n\n\n\n

A data mining specialist uses their statistics and machine learning expertise to reveal patterns and insights that can solve problems. They swift through huge amounts of data, applying algorithms and data mining techniques to identify correlations and anomalies. In addition to these, data mining specialists are also essential for organizations to predict future trends and behaviors.<\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

If you want to land a career in data mining, you should possess a degree or have a solid background in computer science, statistics, or a related field. <\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

Data mining specialists earn $109,023<\/a> per year.<\/p>\n\n\n\n

Data Visualisation Engineer<\/h3>\n\n\n\n

Data visualisation engineers specialize in transforming data into visually appealing graphical representations, much like a data storyteller. A big part of their day involves working with data analysts and business teams to understand the data\u2019s context. <\/p>\n\n\n\n

General Requirements<\/h4>\n\n\n\n

Data visualization engineers need a strong foundation in data analysis and be proficient in programming languages often used in data visualization, such as JavaScript, Python, or R. A valuable addition to their already-existing experience is a bit of expertise in design principles to allow them to create visualizations.<\/p>\n\n\n\n

Average Salary<\/h4>\n\n\n\n

The average annual pay of a data visualization engineer is $103,031<\/a>.<\/p>\n\n\n\n

Resources To Find Data Science Jobs<\/h2>\n\n\n\n

The key to finding a good data science job is knowing where to look without procrastinating. To make sure you leverage the right platforms, read on.<\/p>\n\n\n\n

Job Boards<\/h3>\n\n\n\n

When hunting for data science jobs, both niche job boards and general ones can be treasure troves of opportunity. <\/p>\n\n\n\n

Niche boards are created specifically for data science and related fields, offering listings that cut through the noise of broader job markets. Meanwhile, general job boards can have hidden gems and opportunities.<\/p>\n\n\n\n

Online Communities<\/h3>\n\n\n\n

Spend time on platforms like Slack, Discord, GitHub, or IndieHackers, as they are a space to share knowledge, collaborate on projects, and find job openings posted by community members.<\/p>\n\n\n\n

Network And LinkedIn<\/h3>\n\n\n\n

Don\u2019t forget about socials like LinkedIn or Twitter. The LinkedIn Jobs section, in particular, is a useful resource, offering a wide range of opportunities and the ability to directly reach out to hiring managers or apply for positions. Just make sure not to apply through the \u201cEasy Apply\u201d options, as you\u2019ll be competing with thousands of applicants who bring nothing unique to the table.<\/p>\n\n\n\n

FAQs about Data Science Careers<\/h2>\n\n\n\n

We answer your most frequently asked questions.<\/p>\n\n\n\n

Do I Need A Degree For Data Science?<\/h3>\n\n\n\n

A degree is not a set-in-stone requirement to become a data scientist<\/a>. It\u2019s true many data scientists hold a BA\u2019s or MA\u2019s degree, but these just provide foundational knowledge. It\u2019s up to you to pursue further education through courses or bootcamps or work on projects that enhance your expertise. What matters most is your ability to demonstrate proficiency in data science concepts and tools.<\/p>\n\n\n\n

Does Data Science Need Coding?<\/h3>\n\n\n\n

Yes. Coding is essential for data manipulation and analysis, especially knowledge of programming languages like Python and R.<\/p>\n\n\n\n

Is Data Science A Lot Of Math?<\/h3>\n\n\n\n

It depends on the career you want to pursue. Data science involves quite a lot of math, particularly in areas like statistics, probability, and linear algebra.<\/p>\n\n\n\n

What Skills Do You Need To Land an Entry-Level Data Science Position?<\/h3>\n\n\n\n

To land an entry-level job in data science, you should be proficient in several areas. As mentioned above, knowledge of programming languages is essential, and you should also have a good understanding of statistical analysis and machine learning. Soft skills are equally valuable, so make sure you\u2019re acing problem-solving, critical thinking, and effective communication.<\/p>\n\n\n\n

Since you\u2019re here\u2026<\/strong>Are you interested in this career track? Investigate with our free guide to what a data professional actually<\/em> does<\/a>. When you\u2019re ready to build a CV that will make hiring managers melt, join our Data Science Bootcamp<\/a> which will help you land a job or your tuition back!<\/p>\n","protected":false},"excerpt":{"rendered":"

This post was written by Nicole Hitner, a content strategist at Exago, Inc., producers of embedded business intelligence for software companies. She manages the company\u2019s content marketing, writes for their blog, and assists the product design team in continuing to enhance Exago BI. SQL Joins Explained A relational database is ultimately just a collection of […]<\/p>\n","protected":false},"author":26,"featured_media":3618,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_eb_data_table":"","footnotes":""},"categories":[67],"tags":[],"marketing_tags":[],"class_list":{"0":"post-3571","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-data-science"},"acf":[],"_links":{"self":[{"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/posts\/3571"}],"collection":[{"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/comments?post=3571"}],"version-history":[{"count":3,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/posts\/3571\/revisions"}],"predecessor-version":[{"id":47550,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/posts\/3571\/revisions\/47550"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/media\/3618"}],"wp:attachment":[{"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/media?parent=3571"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/categories?post=3571"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/tags?post=3571"},{"taxonomy":"marketing_tags","embeddable":true,"href":"https:\/\/www.springboard.com\/blog\/wp-json\/wp\/v2\/marketing_tags?post=3571"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}