Substance is a procedural texture generator which follows shit ton of very simple rules with lots of sliders and drag tools.
Its like UE4, but it has a big library you can study and producing content or do variations is actually very easy once you get it.
You can pipe content over ID maps and texture objects dynamically. Good for simple environments, distant objects or simpler looks.
Substance Painter builds on Dsigner's library and tools and features and allows you to paint materials across all map types directly upon geometry to achieve a desired effect. Its extra work but it will make your work look pretty fucking amazing.
Quixel, like Substance has a photo-realistically designed system but is instead based on physically scanned properties of real materials and it has the benefit of hooking into Photoshop to work. The downside is it can be pretty sluggish on all but the beefiest machine and its incredibly limited in terms of subtle customization and it tends not to know how to deal with UV seams forcing you to fix things manually. It also includes a special tool for producing normalmaps and a tool for turning captured photos into seamless textures by assessing what light does within the image and then extrapolating that to produce estimated heightmaps, diffuse and other complex properties.
Quixel is also shortly, getting a painter tool and the library of available materials is about to be expanded enormously.
That said, you can't create materials from scratch so you're kind of up shit creek without a paddle if you want to create something that's subtle, nuanced or unique -- and ontop of that, Substance if you get good at it can peel away tens of hours of work by working smarter rather than harder: eg, substance is resolution independent so you can produce a 1k or 4k map and it'll procedurally just update from the brush-strokes it remembers.
My recommendation is to get Quixel anyway because its incredibly cheap and then to try out Substance.